CTE, ROW_NUMBER and ROWCOUNT

Question

我正在尝试编写一个存储过程，返回一组数据的页面及其总行数。该存储过程如下所示：

WITH Props AS
(
    SELECT *,
    ROW_NUMBER() OVER (ORDER BY PropertyID) AS RowNumber
    FROM Property
    WHERE PropertyType = @PropertyType AND ...
)   

SELECT * FROM Props 
WHERE RowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND (@PageNumber * @PageSize);

我无法返回行数（最高行号）。

我知道这个问题已经讨论过了（我看到了这篇文章：Efficient way of getting @@rowcount from a query using row_number），但是当我在CTE中添加 COUNT(x) OVER(PARTITION BY 1) 后，性能下降，原本不需要花费时间的查询现在需要很久才能执行完毕。我猜想这是因为每一行都要计算行数？似乎我不能在另一个查询中重复使用CTE。表Props有10万条记录，CTE返回5千条记录。

- David

---

应该重新标记为SQL Server问题。我本可以帮你做到这一点，但你只允许使用5个标签，而我不知道你想要删除哪些标签。 - James Johnson

Answer 1

在T-SQL中应该是这样的：

;WITH Props AS
(
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY PropertyID) AS RowNumber
    FROM Property
    WHERE PropertyType = @PropertyType AND ...
)

, Props2 AS
(
    SELECT COUNT(*) CNT FROM Props
)

-- Now you can use even Props2.CNT
SELECT * FROM Props, Props2
WHERE RowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND (@PageNumber * @PageSize);

现在你已经在每一行中有CNT了...或者你想要不同的东西吗？你想要一个只有计数的第二个结果集吗？那就去做吧！

-- This could be the second result-set of your query.
SELECT COUNT(*) CNT
FROM Property
WHERE PropertyType = @PropertyType AND ...

注意：已经重新编辑，David引用的第一个查询现在已经被删除，查询2现在成为了查询1。

- xanatos

---

1. 不起作用，因为你必须要有一个group by子句。
2. 完美地工作了，我最喜欢它。谢谢。

- David

在我的版本中，我在第一个CTE中添加了COUNT(1)，导致工作表上有800000次读取。使用第二个CTE（就像你在这里做的那样）跳过了所有这些读取，从而产生了极快的查询。 - Guillaume Schuermans

Answer 2

我遇到了相同的问题，想分享一下可以返回页面和总行数的代码。这个问题通过一个临时表得到解决。以下是存储过程的主体部分：

DECLARE @personsPageTable TABLE(
  RowNumber INT, 
  PersonId INT, 
  FirstName NVARCHAR(50), 
  LastName NVARCHAR(50), 
  BirthDate DATE, 
  TotalCount INT);
    
        ;WITH PersonPage AS 
        (
            SELECT 
                 ROW_NUMBER() OVER(ORDER BY persons.Id) RowNumber,
                 Id,
                 FirstName,
                 LastName,
                 BirthDate
            FROM Persons
            WHERE BirthDate >= @BirthDateFrom AND BirthDate <= @BirthDateTo
        ), TotalCount AS( SELECT COUNT(*) AS [Count] FROM PersonPage)
        INSERT INTO @personsPageTable
        SELECT *, (select * from TotalCount) TotalCount FROM PersonPage
        ORDER BY PersonPage.RowNumber ASC
        OFFSET ((@pageNumber - 1) * @pageSize) ROWS
        FETCH NEXT @pageSize ROWS ONLY
    
        SELECT TOP 1 TotalCount FROM @personsPageTable
    
        SELECT 
            PersonId, 
            FirstName, 
            LastName, 
            BirthDate
        FROM @personsPageTable

你可以看到，我将CTE结果和总行数放入临时表中，并选择两个查询。第一个返回总计数，第二个返回带有数据的页面。

- Yara

Answer 3

你想要整个结果集的计数吗？

这个速度上可行吗？

SELECT *,(select MAX(RowNumber) from Props) as MaxRow 
FROM Props 
WHERE RowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1 
    AND (@PageNumber * @PageSize);

- SQLMenace