SQLserver分页的一些方法

 

在 2012 之前的分页基本上两种方法
2000 时代 top n where id not in (top n )。
2005 时代 row_number()  方式。
前者，如果分页过多话，查询后面的页，性能会快速下降，并且是 2 次扫描。
后者，一次扫描，前几页的性能略逊于前者。

一、用 row_number()  进行分页操作

语法：ROW_NUMBER() OVER(ORDER BY COLUMN)

含义：从1开始，对每一条数据排序后返回一个数字,根据这个列进行分页，这样就可以有效防止id列有中断值的情况

下面使用ROW_NUMBER() 查询31到40条数据：

DECLARE @pageSize INT  --每页显示数据条数
DECLARE @pageIndex INT --当前页

--第4页，每页显示10条数据
SET @pageSize = 10
SET @pageIndex = 4
            
SELECT * FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY Created_Time ASC) AS 'RowNumber', * FROM dbo.COM_UserInfo
) AS UserInfo
WHERE RowNumber BETWEEN ( ( ( @pageIndex - 1 ) * @pageSize ) + 1 ) AND ( @pageIndex * @pageSize )

 

二、用Offset-Fetch 进行分页操作（SQLserver 2012 以上版本）

关键字解析：

• Offset子句：用于指定跳过（Skip）的数据行；
• Fetch子句：该子句在Offset子句之后执行，表示在跳过（Sikp）指定数量的数据行之后，返回一定数据量的数据行；
• 执行顺序：Offset子句必须在Order By 子句之后执行，Fetch子句必须在Offset子句之后执行；

分页实现的思路：

1. 在分页实现中，使用Order By子句，按照指定的columns对结果集进行排序；
2. 使用Offset子句跳过前N页：Offset (@PageIndex-1)*@pageSize rows；
3. 使用Fetch子句呈现当前Page：Fetch next @pageSize rows only；

使用Offset-Fetch子句跳过指定数目的数据行之后，返回指定数目的数据行

select * from table 
order by id ase 
offset 10 rows
Fetch next 10 rows only

修改成分页的通用格式

--分页的索引，页码从0开始
Declare @PageIndex int
--每页显示的行数
Declare @pageSize int

set @PageIndex=1
set @pageSize=2

select * 
from dbo.dt_test
order by id
OFFSET @PageIndex*@pageSize ROWS
FETCH next @pageSize rows only

参考：https://www.cnblogs.com/ljhdo/p/4861263.html