SQL CTE，ROW_NUMBER 和 ROWCOUNT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7436732/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CTE, ROW_NUMBER and ROWCOUNT
提问by David
I am trying to return a page of data and also row count of all data in one stored procedure which looks like following:
我正在尝试返回一页数据以及一个存储过程中所有数据的行数，如下所示：
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);
I am unable to return the row count (highest row number).
我无法返回行数（最高行数）。
I know this has already been discussed (I've seen this: Efficient way of getting @@rowcount from a query using row_number) but when I add COUNT(x) OVER(PARTITION BY 1)in the CTE performance degrades and the query above that normally takes no time takes forever to execute. I reckon it's because the count is calculated for each row? I seems that I can't reuse the CTE in another query. Table Props has 100k records, CTE returns 5k records.
我知道这已经讨论过（我已经看到了： 使用 row_number 从查询中获取 @@rowcount 的有效方法）但是当我在 CTE 中添加COUNT(x) OVER(PARTITION BY 1)时，性能会下降，并且上面的查询这通常不需要永远执行。我认为这是因为计数是为每一行计算的？我似乎无法在另一个查询中重用 CTE。Table Props 有 100k 条记录，CTE 返回 5k 条记录。
回答by xanatos
In T-SQL it should be
在 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);
now you have CNT in every line... Or you wanted something different? You wanted a second resultset with only the count? Then do it!
现在你每行都有 CNT... 或者你想要不同的东西？你想要一个只有计数的第二个结果集？那就去做吧！
-- This could be the second result-set of your query.
SELECT COUNT(*) CNT
FROM Property
WHERE PropertyType = @PropertyType AND ...
Note: reedited, the query 1 David was referencing now has been trashcanned, query 2 is now query 1.
注意：重新编辑，David 现在引用的查询 1 已被删除，查询 2 现在是查询 1。
回答by SQLMenace
You want the count for the whole resultset right?
你想要整个结果集的计数吗？
does this work speedwise?
这是否快速有效？
SELECT *,(select MAX(RowNumber) from Props) as MaxRow
FROM Props
WHERE RowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1
AND (@PageNumber * @PageSize);
回答by Yara
I stuck with the same problem and want to share trick how to return page and total rows. The problem fixed by a temporary table. Here is the body of the stored procedure:
我遇到了同样的问题，想分享如何返回 page 和 total rows 的技巧。问题由临时表修复。这是存储过程的主体：
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
As you can see I put CTE result and total rowsinto the temporary table and select two queries. The first return total count and the second return page with data.
如您所见，我将 CTE结果和总行数放入临时表并选择了两个查询。第一个返回总计数和带有数据的第二个返回页。
