SQL 常见的几种分页

摘自：https://www.cnblogs.com/nov5026/p/4732520.html

1. top/in

select top 10 * from UserLoginInfo
where id not in (select top 100 id from UserLoginInfo order by id)
order by id

2.top/exists
select top 10 * from UserLoginInfo
where not exists
(select 1 from (select top 100 id from UserLoginInfo order by id)a  where a.id=UserLoginInfo.id)
order by id

3.top/max
select top 10 * from UserLoginInfo
where id>(select max(id) from (select top 100 id from UserLoginInfo)a)

4.top/row_number
select top 10 * from
(select row_number()over(order by id)rownumber,* from UserLoginInfo)a
where rownumber>100

5.row_number/
select * from
(select row_number()over(order by id)rownumber,* from UserLoginInfo)a
where rownumber between 100 and 110
 

结论：

1.max/top,ROW_NUMBER()都是比较不错的分页方法。相比ROW_NUMBER()只支持sql2005及以上版本，max/top有更好的可移植性，能同时适用于sql2000,access。

2.not exists感觉是要比not in效率高一点点。

3.ROW_NUMBER()的3种不同写法效率看起来差不多。

4.特大数据量时order by会非常影响查询速度，row_number效率反而要查很多，而且在查询语句中尽量不用order by

本文移植自别处，笔者此处整理为方便自己理解和牢记