sql - 选择COUNT(*)个子查询，而不运行两次

Question

我有一个过程来返回结果集，该结果集受页码和其他一些东西的限制。作为输出参数，我需要根据除页面编号以外的参数返回选定行的总数。所以我有这样的事情:

WITH SelectedItems AS
(SELECT Id, Row1, Row2, ROW_NUMBER() OVER (ORDER BY Row1) AS Position
FROM Items
WHERE Row2 = @Row2)
SELECT Id, Row1, Row2
FROM SelectedItems
WHERE Position BETWEEN @From AND @To

然后，我需要将OUTPUT参数设置为innerquery中的行数。我可以只复制查询并对其进行计数，但是此查询可能返回数千行(将来还会更多)，因此我正在寻找一种性能良好的方法。我在考虑表变量，这是个好主意吗？或其他建议？

更具体地说，它是Microsoft SQL Server 2008。

谢谢你，简

Answer 1

您可以使用COUNT(*)在主查询中将总行计为单独的列。像这样:

WITH SelectedItems AS
(SELECT Id, Row1, Row2, ROW_NUMBER() OVER (ORDER BY Row1) AS Position, 
COUNT(*) OVER () AS TotalRows
FROM Items
WHERE Row2 = @Row2)
SELECT Id, Row1, Row2
FROM SelectedItems
WHERE Position BETWEEN @From AND @To

这将在结果集中而不是在输出参数中返回计数，但这应符合您的要求。否则，请结合临时表:

DECLARE @tmp TABLE (Id int, RowNum int, TotalRows int);

WITH SelectedItems AS
(SELECT Id, Row1, Row2, ROW_NUMBER() OVER (ORDER BY Row1) AS Position, 
COUNT(*) OVER () AS TotalRows
FROM Items
WHERE Row2 = @Row2)
INSERT @tmp
SELECT Id, Row1, Row2
FROM SelectedItems
WHERE Position BETWEEN @From AND @To

SELECT TOP 1 @TotalRows = TotalRows FROM @tmp
SELECT * FROM @tmp

您会发现仅将临时表用于分页结果不会占用太多内存(取决于页面的大小)，而只是将其保留很短的时间。从临时表中选择完整的结果集并选择TotalRows只会花费一点时间。

这比运行完全独立的查询要快得多，在我的测试中(重复使用WITH)，查询的执行时间增加了一倍。