PageSize = 30
PageNumber = 201
方法一:(最常用的分页代码, top / not in)
select top 30 UserId from UserInfo where UserId not in (select top 6000 UserId from UserInfo order by UserId) order by UserId
备注: 注意前后的order by 一致
方法二:(not exists, not in 的另一种写法而已)
select top 30 * from UserLog where not exists (select 1 from (select top 6000 LogId from UserLog order by LogId) a where a.LogId = UserLog.LogId) order by LogId
备注:EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False。此处的 select 1 from 也可以是select 2 from,select LogId from, select * from 等等,不影响查询。而且select 1 效率最高,不用查字典表。效率值比较:1 > anycol > *
方法三:(top / max, 局限于使用可比较列排序的时候)
select top 30 * from UserLog where LogId > (select max(LogId) from (select top 6000 LogId from UserLog order by LogId) a ) order by LogId
备注:这里max()函数也可以用于文本列,文本列的比较会根据字母顺序排列,数字 < 字母(无视大小写) < 中文字符
方法四:(row_number() over (order by LogId))
select top 30 * from ( select row_number() over (order by LogId) as rownumber,* from UserLog)a where rownumber > 6000 order by LogId
select * from (select row_number()over(order by LogId) as rownumber,* from UserLog)a where rownumber > 6000 and rownumber < 6030 order by LogId
select * from (select row_number()over(order by LogId) as rownumber,* from UserLog)a where rownumber between 6000 and 6030 order by LogId
select * from ( select row_number()over(order by tempColumn)rownumber,* from (select top 6030 tempColumn=0,* from UserLog where 1=1 order by LogId)a )b where rownumber>6000 row_number() 的变体,不基于已有字段产生记录序号,先按条件筛选以及排好序,再在结果集上给一常量列用于产生记录序号 以上几种方法参考http://www.cnblogs.com/songjianpin/articles/3489050.html
备注: 这里rownumber方法属于排名开窗函数(sum, min, avg等属于聚合开窗函数,ORACLE中叫分析函数,参考文章:SQL SERVER 开窗函数简介 )的一种,搭配over关键字使用。
方法五:(offset /fetch next, SQL Server 2012支持)
select * from UserLog Order by LogId offset 6000 rows fetch next 30 rows only
备注: 性能参考文章《SQL Server 2012使用OFFSET/FETCH NEXT分页及性能测试》
参考文档:
1、http://blog.csdn.net/qiaqia609/article/details/41445233
2、http://www.cnblogs.com/songjianpin/articles/3489050.html
3、http://database.51cto.com/art/201108/283399.htm
转自:http://www.cnblogs.com/shengxincai/p/6097588.html。