代码段——SQL Server 分页语句
SQL Server 分页语句
目录
0. 使用Top
- 适应于Sql Server2005以前版本
- 相对性能有损失
- 原理：按照指定的字段排序取前前面所有页的数据，再总表中按相同字段排序取排除前面所有页的数据，取页面数的记录
DECLARE @pageSize INT = 100;
DECLARE @pageIndex INT = 2;
SELECT TOP(SELECT @pageSize) *
FROM 目标表
WHERE CreateTime NOT IN
(SELECT TOP(SELECT @pageSize * (@pageIndex - 1)) CreateTime FROM 目标表 ORDER BY CreateTime)
ORDER BY CreateTime;
1. 使用Row_Number()函数+Top()函数
- Row_Nubmer()函数是SQL Server2005版本及以上才有
DECLARE @pageSize INT = 100;--页面记录数量
DECLARE @pageIndex INT = 2;--页数
SELECT TOP(SELECT @pageSize) * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY CreateTime DESC ) AS RowNum,* FROM 查询表) temp
WHERE RowNum > ((@pageIndex - 1) * @pageSize);
SELECT COUNT(1) AS TotalCount FROM 目标表--返回查询结果的总行数
2. 仅使用Row_Number()函数
DECLARE @pageSize INT = 100;
DECLARE @pageIndex INT = 2;
SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY CreateTime DESC ) AS RowNum,* FROM 查询表) temp
WHERE RowNum BETWEEN (@pageIndex-1)* @pageSize+1 AND @pageIndex * @pageSize--计算取值范围并筛选取值
SELECT COUNT(1) AS TotalCount FROM 目标表--返回查询结果的总行数
3. 使用 offset /fetch next
- offset 、fetch next 需要 SQL Server 2012版本及以上才有
- 语法：
ORDER BY column_list [ASC |DESC]
[OFFSET offset_row_count {ROW | ROWS}
[FETCH {FIRST | NEXT} fetch_row_count {ROW | ROWS} ONLY]]
这里FIRST与NEXT是同义的，可以互换使用。ROW 和 ROWS 也是同义的，可以互用。 注意使用ROWS则查询结果当前排序的逆序排列
offset_row_count：可以是大于或等于0的常量(constant)、变量(variable)或参数(parameter)；
fetch_row_count： 可以是大于或等于1的常量(constant)、变量(variable)或标量(scalar)。
注意：
offset和 fetch 仅可以在order by子句后面才能使用，不能与top一起使用，(用“offset 0 fetch next N ROWS ONLY”可以替代“top N”的功能)；
fetch是可选项，用于限制返回行数，只能跟在offset后面使用。
--分页
--简而言之：offset指跳过多少行，fetch next指取多少行
--用OFFSET 和 FETCH 子句实现分页功能比用ROW_NUMBER()函数做子查询的方式会少耗一些资源，性能更好。
-- 分页查询（通用型）
DECLARE @pageIndex INT = 2;
DECLARE @pageSize INT = 100;
SELECT *
FROM dbo.DeviceList
ORDER BY CreateTime OFFSET ((@pageIndex - 1) * @pageSize) ROW FETCH NEXT @pageSize ROW ONLY;
4. 关于如何获取分页结果的集的同时获取总行数
SELECT *
FROM(SELECT ROW_NUMBER() OVER (ORDER BY Id DESC) rn, COUNT(1) OVER () AS TotalCount, Id
FROM dbo.AC_DDApi_Log) a
WHERE a.rn BETWEEN 1 AND 5;
