sql单表简单的分页脚本(续)

上次讲到了简单的分页用到的是not in

接下来用到 max 或者 min

还有sql server 2005以上的 row_number()系统函数

 

select * from Xk_FriendLink
--取出前面的最大id
select max(id) from (select top 2 id from Xk_FriendLink order by id asc) as t
--顺序时从小到大
select top 2 * from Xk_FriendLink where id>(select max(id) from (select top 2 id from Xk_FriendLink order by id asc) as t) order by id asc

--Row_Number()  自增的,不重复，中间不会有断点的，连续的
select *,row_number() OVER (order by id desc) as RowNumber from Xk_FriendLink
select row_number() OVER (order by id desc) as RowNumber from Xk_FriendLink

--每页大小 PageSize=3,页码 PageIndex=2,between PageSize*(PageIndex-1)+1 and PageSize*PageIndex
declare @PageSize int
set @PageSize=3
declare @PageIndex int
set @PageIndex=2
declare @startRow int
set @startRow=@PageSize*(@PageIndex-1)+1
declare @endRow int
set @endRow=@PageSize*@PageIndex
print @startRow
print @endRow

select * from (select *,row_number() OVER (order by id desc) as RowNumber from Xk_FriendLink)
as tmp where RowNumber between @startRow and @endRow