（3.15）常用知识-sql server分页

参考文章：分页写法小结

推荐使用row_number over()方法，或2012以上使用offset

 

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)awhere rownumber > 6000 order by LogId
　　select * from (select row_number()over(order by LogId) as rownumber,* from UserLog)awhere rownumber > 6000 and rownumber < 6030 order by LogId
　　select * from (select row_number()over(order by LogId) as rownumber,* from UserLog)awhere 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)bwhere rownumber>6000

 

 

今天无聊和朋友讨论分页，发现网上好多都是错的。网上经常查到的那个Top Not in 或者Max 大部分都不实用，很多都忽略了Order和性能问题。为此上网查了查，顺带把2000和2012版本的也补上了。

先说说网上常见SQL的错误或者说局限问题

1 2 3 4 5

select  top  10 * from  table1 where  id  not  in (      select  top  开始的位置 id      from  table1)

这样的确是可以取到分页数据，但是这是默认排序的，如果要按其中一列排序呢？那order by 加在哪里呢？里外都加，显然不行，外面的Order不起作用，只能嵌套，Oh my god,编程三个Select了，这效率。

为了好用效率高，总体思路还是老老实实的用RowNumber解决，但是SQL2000没有RowNumber，其实我们可以通过临时表自增列搞定，不多说，上例子。

 

SQL 2000 用临时表解决，通过在临时表中增加自增列解决RowNumber。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

DECLARE  @Start  INT DECLARE  @ End  INT SELECT  @Start = 14000,@ End  = 14050     CREATE  TABLE  #employees (RowNumber  INT  IDENTITY(1,1), LastName  VARCHAR (100),FirstName  VARCHAR (100), EmailAddress  VARCHAR (100))     INSERT  INTO  #employees (LastName, FirstName, EmailAddress) SELECT  LastName, FirstName, EmailAddress FROM  Employee ORDER  BY  LastName, FirstName, EmailAddress SELECT  LastName, FirstName, EmailAddress FROM  #employees WHERE  RowNumber > @Start  AND  RowNumber <= @ End     DROP  TABLE  #employees     GO

　　

SQL 2005/2008 由于支持了Row_Number于是通过派生表的方式解决（两个嵌套）

1 2 3 4 5 6 7 8 9 10 11 12

DECLARE  @Start  INT DECLARE  @ End  INT SELECT  @Start = 14000,@ End  = 14050     SELECT  LastName, FirstName, EmailAddress FROM  ( SELECT  LastName, FirstName, EmailAddress, ROW_NUMBER() OVER ( ORDER  BY  LastName, FirstName, EmailAddress)  AS  RowNumber FROM  Employee) EmployeePage WHERE  RowNumber > @Start  AND  RowNumber <= @ End ORDER  BY  LastName, FirstName, EmailAddress GO

　　

SQL 2005/2008 或者用CTE的方式实现，和派生表一样，就是好看点，执行计划都一样。

1 2 3 4 5 6 7 8 9 10 11 12 13 14

DECLARE  @Start  INT DECLARE  @ End  INT SELECT  @Start = 14000,@ End  = 14050;     WITH  EmployeePage  AS ( SELECT  LastName, FirstName, EmailAddress, ROW_NUMBER() OVER ( ORDER  BY  LastName, FirstName, EmailAddress)  AS  RowNumber FROM  Employee) SELECT  LastName, FirstName, EmailAddress FROM  EmployeePage WHERE  RowNumber > @Start  AND  RowNumber <= @ End ORDER  BY  LastName, FirstName, EmailAddress GO

 

ROW_NUMBER 比较好用的办法

--（1）在ROW_NUMBER的同时用COUNT计算总行数
declare @page_no int
declare @page_size int

set @page_no = 2
set @page_size = 10

;WITH tmp
AS
(
select *, 
ROW_NUMBER() OVER(order by ID) num, 
COUNT(*) OVER() total
from split_pages
)
select ID, Name
from tmp
where num BETWEEN (@page_size*(@page_no-1)+1) AND @page_size*@page_no
order by num

--（2）仅使用ROW_NUMBER计算总行数，IO最少
declare @page_no int
declare @page_size int

set @page_no = 2
set @page_size = 10

;with tmp
as
(
select *, 
ROW_NUMBER() OVER(order by ID) num, 
ROW_NUMBER() OVER(order by ID desc) num_desc
from split_pages
)
select ID, Name, num_desc + num -1 as total
from tmp
where num BETWEEN (@page_size*(@page_no-1)+1) AND @page_size*@page_no
order by num

 

SQL SERVER 2012 比较给力支持了OFFSET，于是一个Select结束战斗

1 2 3 4 5

SELECT  LastName, FirstName, EmailAddress FROM  Employee ORDER  BY  LastName, FirstName, EmailAddress OFFSET 14000  ROWS FETCH  NEXT  50  ROWS  ONLY ;

　　

 

最后说下，根据老外的文章，在2012里，如果前面加上TOP（50），那么执行计划就会少读很多行数据（读的精准了），提高性能。但是鉴于本人手头没2012也无法测试。至少在2008R2上加不加TOP执行计划都一样。

 

 

转自：https://www.cnblogs.com/ebread/p/SQLServer.html

转载于:https://www.cnblogs.com/gered/p/9197279.html