提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  后端

当前栏目

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

serverSQL 查询 常用 知识 分页
2023-09-11 14:21:10 时间

参考文章：分页写法小结

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

方法一：(最常用的分页代码， 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

 

【Sql server2000】分页办法推荐

（1）传统的top not in方式的弊端

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

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

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

 

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

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

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

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 server2005~~Sql server2008】分页办法推荐

（1）使用row_number 构建派生表

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

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

 

（2）使用CTE+Row_number

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

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

（3）row_number分页实践（推荐）

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 server2012及以上】推荐分页方法

（1）offset fetch next

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

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

代码中

OFFSET 14000 ROWS
FETCH NEXT 50 ROWS ONLY;

意思是，从上述 select 结果集中，第14000行开始读取，取接下来的50行。

和mysql中的 limit m,n 一样。从第 m 行开始读取，取接下来的 n 行

　　

 

最后说下，根据老外的文章，在2012里，如果前面加上TOP（50），那么执行计划就会少读很多行数据（读的精准了），提高性能。这个还没有测试过、。

 

 

参考：https://www.cnblogs.com/ebread/p/SQLServer.html

相关文章