首页 > 代码库 > 07. 分页写法小结
07. 分页写法小结
分页的实现方式有这几种：
1. 在前台程序中，将所有的记录都读到本地，前台程序通过游标在数据集中上下移动，数据量大的话，性能很差，不推荐；
2. 前台程序请求某一页数据时，到数据库做一次查询，返回符合条件的相应记录，这也是目前常用的方法；
3. 对方式2的改进，当请求某一页时，同时将前后几页一并返回，用户翻页时就不需要反复请求数据库了。
对于2，3的实现，随着SQL Server版本的升级，常用的方法有三种：TOP，ROW_NUMBER，OFFSET/FETCH NEXT。
测试数据：
if OBJECT_ID(‘split_pages‘) is not nulldrop table split_pagesGOcreate table split_pages (ID int primary key,Name varchar(100) )GOdeclare @i intset @i=1while @i<=300begininsert into split_pagesselect @i,‘TEST‘ + CAST(@i as varchar(10))set @i=@i+10endGOselect * from split_pages
一. TOP
SQL Server 2000时多用，另外，有人用过set rowcount来分页，原理类似TOP，不过需要反复设置set rowcount，不方便，而且set rowcount中的行数不能参与select语句的执行计划，只能起到控制行数的作用。
1. 哪种TOP写法更高效
(1) 利用order by正反排序
select * from (select top 10 * from (select top 20 * from split_pages order by ID) a order by ID desc) b order by ID
公式为：
declare @page_no intdeclare @page_size intselect * from (select top @page_size * from (select top @page_size*@page_no * from split_pages order by ID) a order by ID desc) b order by ID
做完最里层select后，再对派生表查询时，index就没有效果了，而且越往后面要top更多的数据，这种写法会更慢。
(2) 利用NOT IN或者NOT EXISTS
select top 10 *from split_pageswhere ID NOT IN (select top 10 ID from split_pages order by ID)order by ID
公式为：
declare @page_no intdeclare @page_size intselect top @page_size *from split_pageswhere ID NOT IN (select top @page_size*(@page_no-1) ID from split_pages order by ID)order by ID
通常在写SQL语句时，用IN/EXISTS不一样，如果逻辑不变的话， EXISTS的效率高。
不过，利用NOT IN分页，和用NOT EXISTS效果基本一样，因为都需要扫完全部数据。
(3) 利用ID大于MAX(ID)
select top 10 *from split_pageswhere ID > (select MAX(ID) from (select top 10 ID from split_pages order by ID) t)order by ID
公式为：
declare @page_no intdeclare @page_size intselect top @page_size *from split_pageswhere ID > (select MAX(ID) from (select top @page_size*(@page_no-1) ID from split_pages order by ID) t)order by ID
在使用TOP分页时，这种用法效率最高。
2. SQL Server 2000与2005中TOP的区别
(1) SQL Server 2000时，top不支持变量，所以分页时，这些语句都要改成动态SQL
如下：
declare @page_no intdeclare @page_size intdeclare @sql varchar(8000)set @page_no = 2set @page_size = 10set @sql = ‘select top ‘ + str(@page_size) + ‘*from split_pages ‘ + ‘where ID > (select MAX(ID) from (select top ‘ + str(@page_size*(@page_no-1))+ ‘ ID from split_pages order by ID) t)order by ID‘exec(@sql)
(2) SQL Server 2005开始，top可以直接使用变量
如下：
declare @page_no intdeclare @page_size intset @page_no = 2set @page_size = 10select top (@page_size) *from split_pageswhere ID NOT IN (select top (@page_size*(@page_no-1)) ID from split_pages order by ID)order by ID
二. ROW_NUMBER
SQL Server 2005开始的新语法，和ORACLE，DB2中的row_number()类似。性能比用TOP有所提升。
在利用ROW_NUMBER分页时，总页数/行数的计算，可以有这几种写法。
(1) 单独的SQL语句去获得总行数
select COUNT(*) AS TotRowsfrom split_pagesGOdeclare @page_no intdeclare @page_size intset @page_no = 2set @page_size = 10;with tmpAS(select *,ROW_NUMBER() OVER(order by ID) numfrom split_pages)select ID, Namefrom tmpwhere num BETWEEN (@page_size*(@page_no-1)+1) AND @page_size*@page_noorder by num
(2) 在ROW_NUMBER的同时用COUNT计算总行数
declare @page_no intdeclare @page_size intset @page_no = 2set @page_size = 10;WITH tmpAS(select *, ROW_NUMBER() OVER(order by ID) num, COUNT(*) OVER() totalfrom split_pages)select ID, Namefrom tmpwhere num BETWEEN (@page_size*(@page_no-1)+1) AND @page_size*@page_noorder by num
(3) 仅使用ROW_NUMBER计算总行数，IO最少
declare @page_no intdeclare @page_size intset @page_no = 2set @page_size = 10;with tmpas(select *, ROW_NUMBER() OVER(order by ID) num, ROW_NUMBER() OVER(order by ID desc) num_descfrom split_pages)select ID, Name, num_desc + num -1 as totalfrom tmpwhere num BETWEEN (@page_size*(@page_no-1)+1) AND @page_size*@page_noorder by num
三. OFFSET/FETCH NEXT
SQL Server 2012的新语法，类似MYSQL，POSTGRESQL中的LIIMIT/OFFSET，据称性能比ROW_NUMBER又有了提升。
declare @page_no intdeclare @page_size intset @page_no = 3set @page_size = 10SELECT *,COUNT(*) OVER() AS TotalFROM split_pagesORDER BY IDOFFSET (@page -1) * @size ROWSFETCH NEXT @size ROWS ONLY;
四. 其他方法
(1) 临时表
在SQL Server 2000时，利用临时表生成rownum来分页，不推荐。
select IDENTITY(int,1,1) as num,* into #t from split_pagesselect * from #t where num between 11 and 20
(2) sp_cursoropen
利用游标，性能较差，原理类似在程序中把数据全部加载到本地，然后通过游标移动来分页。不作推荐。