Getting total row count from OFFSET / FETCH NEXT

因此，我得到了一个函数，它返回一些记录，我希望在我的网站上实现分页。有人建议我使用 SQLServer2012中的 Offset/FetchNext 来实现这一点。在我们的网站上，我们有一个区域，列出总数的记录和什么网页你在时间。

在此之前，我得到了整个记录集，并能够以编程方式在其上构建分页。但是通过使用 SQL 方法和 FETCH NEXT X ROWS ONLY，我只得到了 X 行，所以我不知道我的总记录集是什么，也不知道如何计算我的最小和最大页面。我知道这样做的唯一方法是调用函数两次，对第一次执行行数计算，然后使用 FETCH NEXT 运行第二次。有没有更好的方法，不会让我运行两次查询？我在努力加快表现，而不是放慢速度。

75050 次浏览
小开
最佳答案

你可以使用 COUNT(*) OVER()... 下面是一个使用 sys.all_objects的快速示例:

DECLARE
@PageSize INT = 10,
@PageNum  INT = 1;


SELECT
name, object_id,
overall_count = COUNT(*) OVER()
FROM sys.all_objects
ORDER BY name
OFFSET (@PageNum-1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

但是，这应该保留给小型数据集; 对于大型数据集，性能可能非常糟糕。See this Paul White article for better alternatives，包括维护索引视图(只有在结果未过滤或者您事先知道 WHERE子句的情况下才有效)和使用 ROW_NUMBER()技巧。

小开

I encountered some performance issues using the COUNT(OVER ()方法。(我不确定是不是服务器的问题，因为它花了40秒钟才返回10条记录，之后就没有任何问题了。)这种技术在所有条件下都可以工作，而不必使用 COUNT () OVER() and accomplishes the same thing:

DECLARE
@PageSize INT = 10,
@PageNum  INT = 1;


WITH TempResult AS(
SELECT ID, Name
FROM Table
), TempCount AS (
SELECT COUNT(*) AS MaxRows FROM TempResult
)
SELECT *
FROM TempResult, TempCount
ORDER BY TempResult.Name
OFFSET (@PageNum-1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY
小开

Based on James Moberg's answer:

如果您没有 SQL server 2012并且不能使用 OffSET，那么这是使用 Row_Number()的一种替代方法

DECLARE
@PageNumEnd INT = 10,
@PageNum  INT = 1;


WITH TempResult AS(
SELECT ID, NAME
FROM Tabla
), TempCount AS (
SELECT COUNT(*) AS MaxRows FROM TempResult
)


select *
from
(
SELECT
ROW_NUMBER() OVER ( ORDER BY PolizaId DESC) AS 'NumeroRenglon',
MaxRows,
ID,
Name
FROM TempResult, TempCount


)resultados
WHERE   NumeroRenglon >= @PageNum
AND NumeroRenglon <= @PageNumEnd
ORDER BY NumeroRenglon
小开

显然，根据查询的不同，结果可能有很大差异。我用这些结果测试了我的案例: (8个连接，2个子查询，5800行显示不同的结果，5900行显示不同的结果) :

  • ~ 0.820秒使用 COUNT(1) OVER()(Aaron Bertrand 的回答，但结果错误 *)
  • ~0.850 sec using #TEMP table.
  • ~1.590 sec WITH .. AS (James Moberg's anser)
  • ~ 1.600秒运行两次(第一次没有订购，只是为了计数)

* 在我的情况下，Aaron Bertrand 的答案不工作，因为 COUNT(1) OVER()似乎包括行过滤了 DISTINCT。

使用临时表:

DECLARE
@PageSize INT = 10,
@PageNum  INT = 1;
 

SELECT
name, object_id
INTO #MY_TEMP
FROM sys.all_objects


SELECT *
FROM #MY_TEMP
ORDER BY name
OFFSET (@PageNum-1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;


SELECT COUNT(1) FROM #MY_TEMP
-- or
-- SELECT @MY_OUTPUT_PARAM = COUNT(1) FROM #MY_TEMP


DROP TABLE #MY_TEMP

临时表的优点是可以将计数分隔为不同的结果或输出参数。


提交答案