如何在 SELECT T-SQL 命令的结果集中包含返回的总行数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2821380/
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
How to include the total number of returned rows in the resultset from SELECT T-SQL command?
提问by quarkX
I would like to ask if there is a way to include the total number of rows, as an additional column, in the returned result sets from a TSQL query using also the Row_Number(SQL 2005) command.
For example, getting the results set from a query against Book table in a form similar to this:
我想问一下是否有办法将总行数作为附加列包含在使用Row_Number(SQL 2005) 命令的 TSQL 查询返回的结果集中。例如，以类似于以下形式的对 Book 表的查询获取结果集：
RowNum BookId BookTitle TotalRows
--------------------------------------------
1 1056 Title1 5
2 1467 Title2 5
3 121 Title3 5
4 1789 Title4 5
5 789 Title5 5
The query is part of custom paging functionality implemented in a stored procedure. The goal is to return back only the records for the current page Index and limited to the page size, but also the amount of total number of records in the select statement in order to determine the total number of resultset pages.
该查询是在存储过程中实现的自定义分页功能的一部分。目标是仅返回当前页面索引的记录并限制页面大小，而且还返回 select 语句中的记录总数，以确定结果集页面的总数。
回答by Jon Harbour
In SQL Server 2008 and later, add COUNT(*) OVER () as one of the column names in your query and that will be populated with the total rows returned. It is repeated in every single row but at least the value is available. The reason why many other solutions do not work is that, for very large result sets, you will not know the total until after iterating all rows which is not practical in many cases (especially sequential processing solutions). This technique gives you the total count after calling the first IDataReader.Read(), for instance.
在 SQL Server 2008 及更高版本中，将 COUNT(*) OVER () 添加为查询中的列名之一，并将填充返回的总行数。它在每一行中重复，但至少值是可用的。许多其他解决方案不起作用的原因是，对于非常大的结果集，直到迭代所有行之后您才会知道总数，这在许多情况下是不切实际的（尤其是顺序处理解决方案）。例如，此技术在调用第一个 IDataReader.Read() 后为您提供总数。
select COUNT(*) OVER () as Total_Rows, ... from ...
回答by Mark Byers
In SQL Server 2005 and newer you can do this with a CTE:
在 SQL Server 2005 和更新版本中，您可以使用 CTE 执行此操作：
WITH result AS (SELECT ... your query here ...)
SELECT
*,
(SELECT COUNT(*) FROM result) AS TotalRows
FROM result
In general I'd advise against doing this, but if you really need to then this is how to do it.
一般来说，我建议不要这样做，但如果你真的需要，那么这就是这样做的方法。
回答by Daniel Renshaw
Via comments attached to the question it's clear that this question relates to paging. In that scenario, there are two broad approaches:
通过问题附带的评论，很明显这个问题与分页有关。在这种情况下，有两种广泛的方法：
- Query allrows that match the search criteria (not just one page worth) and store into a table valued variable (along with a ROW_NUMBER). Then run two queries against that table valued variable: one to extract the desired page of data and the second to to get the total count.
- Don't use a table-valued variable and just run the full query twice, once to get the page of data and once for the total count.
- 查询与搜索条件匹配的所有行（不仅仅是一页）并将其存储到表值变量中（连同 ROW_NUMBER）。然后对该表值变量运行两次查询：一次提取所需的数据页，第二次获取总计数。
- 不要使用表值变量，只需运行两次完整查询，一次获取数据页，一次获取总计数。
The first option works well if the total number of rows is measured in the thousands. If the total number is much higher, you best bet is to run the query twice.
如果总行数以千计，则第一个选项效果很好。如果总数要高得多，最好的办法是运行查询两次。
This is a typical space/processing trade-off.
这是典型的空间/处理权衡。
Your milage may vary - what works well for one situation may be terrible in another!
您的里程可能会有所不同 - 在一种情况下有效的方法在另一种情况下可能会很糟糕！
回答by John Sansom
Example using the AdventureWorks database
使用 AdventureWorks 数据库的示例
select
*,
TotalVolume = (select COUNT(*) from HumanResources.Department)
from HumanResources.Department
回答by Richard JP Le Guen
Perhaps what you'er looking for is @@ROWCOUNT?
也许你要找的是@@ROWCOUNT什么？
回答by A-K
SELECT n ,
COUNT(*) OVER ( PARTITION BY 1 )
FROM ( SELECT 1 AS n
UNION ALL
SELECT 2 AS n
) AS t
Note that @@ROWCOUNT gives you row count from the previous command. Run this:
请注意，@@ROWCOUNT 为您提供上一个命令的行数。运行这个：
SELECT 1 AS n;
SELECT n ,
@@ROWCOUNT
FROM ( SELECT 1 AS n
UNION ALL
SELECT 2 AS n
) AS t
回答by SQLDev
select *, @@rowcount from MyTable
