几种常见SQL分页方式
1个回答
展开全部
create table pagetest
(
id int identity(1,1) not null,
col01 int null,
col02 nvarchar(50) null,
col03 datetime null
)
--分页1,not in/top
select top 50 * from pagetest
where id not in (select top 9900 id from pagetest order by id)
order by id
--分页2,not exists
select top 50 * from pagetest
where not exists
(select 1 from (select top 9900 id from pagetest order by id)a where a.id=pagetest.id)
order by id
--写法3,max/top
select top 50 * from pagetest
where id>(select max(id) from (select top 9900 id from pagetest order by id)a)
order by id
--分页4,row_number()
select top 50 * from
(select row_number()over(order by id)rownumber,* from pagetest)a
where rownumber>9900
select * from
(select row_number()over(order by id)rownumber,* from pagetest)a
where rownumber>9900 and rownumber<9951
select * from
(select row_number()over(order by id)rownumber,* from pagetest)a
where rownumber between 9901 and 9950
--分页5,在csdn上一帖子看到的,row_number() 变体,不基于已有字段产生记录序号,先按条件筛选以及排好序,再在结果集上给一常量列用于产生记录序号
select *
from (
select row_number()over(order by tempColumn)rownumber,*
from (select top 9950 tempColumn=0,* from pagetest where 1=1 order by id)a
)b
where rownumber>9900
结论:
1.max/top,ROW_NUMBER()都是比较不错的分页方法。相比ROW_NUMBER()只支持sql2005及以上版本,max/top有更好的可移植性,能同时适用于sql2000,access。
2.not exists感觉是要比not in效率高一点点。
3.ROW_NUMBER()的3种不同写法效率看起来差不多。
4.ROW_NUMBER() 的变体基于这个测试效率实在不好。
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询