網上看了很多關于"上一篇下篇"的文章，可大都是按ID排序。

　　實際上，很少有按ID排序的。

　　分享下我的單獨排序字段的寫法，主要分為ms sql2000 和 ms 2005及以上版本。

　　SQL 2005及以上版本寫法。

 1 declare @currentID int 2 set @currentID=4 3  4 ;with temp 5 as 6 ( 7     select row_number() over( order by short desc , createtime desc) as rowNum, * 8     from ch_ItemInformation  9 )10 select * from temp where rowNum in ((select rowNum from temp where id=@currentID)+1)11 union all 12 select * from temp where rowNum in ((select rowNum from temp where id=@currentID)-1)

SQL 2000寫法

 1 --前提：排序后要唯一，即排序后不能有兩條 2 declare @Num int 3 set @Num=1 4 select @Num=@Num+1 from dbo.ch_ItemInformation 5 where Short>=0 and ID >=10 6 order by Short desc, ID desc 7  8 set @Num=@Num+1 9 select @Num10 11 select top 3 * from (12 select top (select @Num) * from ch_ItemInformation order by Short desc, ID desc13 ) as a14 order by a.Short desc, a.ID desc 

有網友問: 既然使用了row_number() 函數，where 中為何還用in？

說明下: 只是為了找到ID對應的排序后的排序號. 改成等于號也行. 如下:

declare @currentID intset @currentID=1;with tempas(    select row_number() over( order by short desc , createtime desc) as rowNum, *    from dbo.PRoduct )select * from temp where rowNum = ((select rowNum from temp where id=@currentID)+1)union all select * from temp where rowNum = ((select rowNum from temp where id=@currentID)-1)