上一篇下一篇 排序 (非ID字段排序)

　　网上看了很多关于"上一篇下篇"的文章，可大都是按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 @Num
10 
11 select top 3 * from (
12 select top (select @Num) * from ch_ItemInformation order by Short desc, ID desc
13 ) as a
14 order by a.Short desc, a.ID desc

     有网友问: 既然使用了row_number() 函数，where 中为何还用in？

     说明下: 只是为了找到ID对应的排序后的排序号. 改成等于号也行. 如下:

declare @currentID int
set @currentID=1

;with temp
as
(
    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)
posted @ 2015-01-27 15:54  bicijinlian  阅读(342)  评论(1)  编辑  收藏  举报