更新时间:2023-02-20 12:09:52
获取结果总数和分页是两个不同的操作.为了这个示例,我们假设您要处理的查询是
Getting the total number of results and paginating are two different operations. For the sake of this example, let's assume that the query you're dealing with is
SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate
在这种情况下,您可以使用以下方法确定结果总数:
In this case, you would determine the total number of results using:
SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'
...这似乎效率低下,但假设所有索引等均已正确设置,实际上却表现不错.
...which may seem inefficient, but is actually pretty performant, assuming all indexes etc. are properly set up.
接下来,要以分页的方式获取实际结果,以下查询将是最有效的:
Next, to get actual results back in a paged fashion, the following query would be most efficient:
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应用程序,您无需保留任何状态,除了要返回的行号.
This will return rows 1-19 of the original query. The cool thing here, especially for web apps, is that you don't have to keep any state, except the row numbers to be returned.