在SQL Server 2000、2005、2008、2012中分页结果的最佳方法(性能方面)是什么?如果你还想获得结果的总数(在分页之前)?
当前回答
用例方面,以下内容似乎易于使用和快速。只需设置页码。
use AdventureWorks
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6;
with result as(
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail
where 1=1
)
select SalesOrderDetailID, SalesOrderID, ProductID from result
WHERE result.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)
同样没有CTE
use AdventureWorks
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM (
SELECT SalesOrderDetailID, SalesOrderID, ProductID,
ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail
where 1=1
) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)
其他回答
最后,微软SQL Server 2012发布了,我真的很喜欢它的简单的分页,你不需要使用复杂的查询,就像这里回答的。
要获得接下来的10行,只需运行这个查询:
SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql#using-offset-and-fetch-to-limit-the-rows-returned
使用时需要考虑的要点:
ORDER BY是强制使用OFFSET…获取子句。 OFFSET子句在FETCH中是强制的。你不能使用ORDER BY… 取回。 在同一个查询中,TOP不能与OFFSET和FETCH组合使用 表达式。
您没有指定使用的语言或驱动程序。因此我只是抽象地描述它。
创建可滚动的结果集/数据集。这要求在表上有一个(多个)主表 跳到最后 请求行数 跳转到页面的开头 滚动行直到页面的末尾
从2012年起,我们可以使用 偏移10行只获取下10行
获取结果总数和分页是两个不同的操作。对于这个示例,让我们假设您正在处理的查询是
SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate
在这种情况下,您将使用以下命令确定结果的总数:
SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'
...这可能看起来效率很低,但实际上性能很好,假设所有索引等都正确设置。
接下来,要以分页方式返回实际结果,下面的查询将是最有效的:
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
FROM Orders
WHERE OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE RowNum >= 1
AND RowNum < 20
ORDER BY RowNum
这将返回原始查询的第1-19行。这里很酷的事情是,尤其是对于web应用程序,你不需要保留任何状态,除了要返回的行号。
在sql server 2012中分页的最佳方法是在存储过程中使用offset和fetch next。 OFFSET关键字-如果对order by子句使用OFFSET,则查询将跳过OFFSET n Rows中指定的记录数。
FETCH NEXT关键字-当我们使用FETCH NEXT与一个order by子句,它将返回你想要显示在分页的行数,没有Offset,那么SQL将生成一个错误。 下面是一个例子。
create procedure sp_paging
(
@pageno as int,
@records as int
)
as
begin
declare @offsetcount as int
set @offsetcount=(@pageno-1)*@records
select id,bs,variable from salary order by id offset @offsetcount rows fetch Next @records rows only
end
您可以像下面这样执行它。
exec sp_paging 2,3
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 阅读GHC核心
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- Python: List vs Dict用于查找表
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 为什么MATLAB的矩阵乘法运算这么快?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- for循环和for-each循环在性能上有区别吗?