SQL 在sql server中获取包括列值的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8099512/
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
Get row count including column values in sql server
提问by c-chavez
I need to get the row count of a query, and also get the query's columns in one single query. The count should be a part of the result's columns (It should be the same for all rows, since it's the total).
我需要获取查询的行数，并在一个查询中获取查询的列。计数应该是结果列的一部分（所有行都应该相同，因为它是总数）。
for example, if I do this:
select count(1) from tableI can have the total number of rows.
例如，如果我这样做：
select count(1) from table我可以获得总行数。
If I do this:
select a,b,c from tableI'll get the column's values for the query.
如果我这样做：
select a,b,c from table我将获得查询的列值。
What I need is to get the count and the columns values in one query, with a very effective way.
我需要的是以一种非常有效的方式在一个查询中获取计数和列值。
For example:
select Count(1), a,b,c from tablewith no group by, since I want the total.
例如：
select Count(1), a,b,c from table没有分组，因为我想要总数。
The only way I've found is to do a temp table (using variables), insert the query's result, then count, then returning the join of both. But if the result gets thousands of records, that wouldn't be very efficient.
我发现的唯一方法是做一个临时表（使用变量），插入查询的结果，然后计数，然后返回两者的连接。但是如果结果有数千条记录，那效率就不会很高。
Any ideas?
有任何想法吗？
回答by Damien_The_Unbeliever
@Jim H is almost right, but chooses the wrong ranking function:
@Jim H 几乎是正确的，但选择了错误的排名函数：
create table #T (ID int)
insert into #T (ID)
select 1 union all
select 2 union all
select 3
select ID,COUNT(*) OVER (PARTITION BY 1) as RowCnt from #T
drop table #T
Results:
结果：
ID RowCnt
1 3
2 3
3 3
Partitioning by a constant makes it count over the whole resultset.
按常量进行分区使其计入整个结果集。
回答by OMG Ponies
Using CROSS JOIN:
使用交叉连接：
SELECT a.*, b.numRows
FROM YOUR_TABLE a
CROSS JOIN (SELECT COUNT(*) AS numRows
FROM YOUR_TABLE) b
回答by Jim H.
回答by PaulStock
You could do it like this:
你可以这样做：
SELECT x.total, a, b, c
FROM
table
JOIN (SELECT total = COUNT(*) FROM table) AS x ON 1=1
which will return the total number of records in the first column, followed by fields a,b & c
这将返回第一列中的记录总数，然后是字段 a、b 和 c
