Sql：从(select ...)中选择count(*)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22971633/
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
Sql: Select count(*) from (select ...)
提问by Mahmoodvcs
I have a sql select command with grouping and I want to get the number of total rows. How do I achieve that?
我有一个带分组的 sql select 命令，我想获取总行数。我如何做到这一点？
My sql command:
我的sql命令：
select p.UserName, p.FirstName + ' ' + p.LastName as [FullName]
,count(b.billid) as [Count], sum(b.PercentRials) as [Sum] from Bills b
inner join UserProfiles p on b.PayerUserName=p.UserName
where b.Successful=1
group by p.UserName, p.FirstName + ' ' + p.LastName
I have tried these with no luck:
我试过这些没有运气：
select count(*) from (select ...)
and
和
select count(select ...)
EDIT
编辑
this is the complete sql statement that I want to run:
这是我要运行的完整 sql 语句：
select count(*) from ( select p.UserName, p.FirstName + ' ' + p.LastName as [FullName]
,count(b.billid) as [Count], sum(b.PercentRials) as [Sum] from Bills b
inner join UserProfiles p on b.PayerUserName=p.UserName
where b.Successful=1
group by p.UserName, p.FirstName + ' ' + p.LastName)
and I get this error on the last line:
我在最后一行收到此错误：
Incorrect syntax near ')'.
回答by M.Ali
SELECT COUNT(*)
FROM
(
select p.UserName, p.FirstName + ' ' + p.LastName as [FullName]
,count(b.billid) as [Count], sum(b.PercentRials) as [Sum] from Bills b
inner join UserProfiles p on b.PayerUserName=p.UserName
where b.Successful=1
group by p.UserName, p.FirstName + ' ' + p.LastName --<-- Removed the extra comma here
) A --<-- Use an Alias here
As I expected from your shown attempt you were missing an Alias
正如我从你展示的尝试中所期望的那样，你错过了一个别名
select count(*)
from (select ...) Q --<-- This sub-query in From clause needs an Alias
Edit
编辑
If you only need to know the rows returned by this query and you are executing this query anyway somwhere in your code you could simply make use of @@ROWCOUNTfunction. Something like....
如果您只需要知道此查询返回的行，并且您正在代码中的某个地方执行此查询，则可以简单地使用@@ROWCOUNT函数。就像是....
SELECT ...... --<-- Your Query
SELECT @@ROWCOUNT --<-- This will return the number of rows returned
-- by the previous query
回答by Mahmoodvcs
Try this code:
试试这个代码：
SELECT COUNT(*)
FROM (
SELECT p.UserName
,p.FirstName + ' ' + p.LastName AS [FullName]
,COUNT(b.billid) AS [Count]
,SUM(b.PercentRials) AS [Sum]
FROM Bills b
INNER JOIN UserProfiles p
ON b.PayerUserName = p.UserName
WHERE b.Successful = 1
GROUP BY p.UserName
,p.FirstName + ' ' + p.LastName
) a
based on your edit. You were missing derived tabled alias.
根据您的编辑。您缺少派生的表别名。
If you look at FROMclause syntax you will see
如果您查看FROM子句语法，您会看到
| derived_table [ AS ] table_alias[ ( column_alias [ ,...n ] ) ]
When a derived table, rowset or table-valued function, or operator clause (such as PIVOT or UNPIVOT) is used, the required table_aliasat the end of the clause is the associated table name for all columns, including grouping columns, returned.
| 派生表 [AS] 表别名[（列别名[,...n]）]
当使用派生表、行集或表值函数或运算符子句（例如 PIVOT 或 UNPIVOT）时，子句末尾所需的 table_alias是返回的所有列（包括分组列）的关联表名称。
回答by rs.
You are missing an alias after your subquery
您在子查询后缺少别名
select count(*) from (select ...) v
You can also try to return total count using existing SQL, without using subquery
您还可以尝试使用现有 SQL 返回总计数，而不使用子查询
select p.UserName,
p.FirstName + ' ' + p.LastName as [FullName],
count(b.billid) as [Count],
sum(b.PercentRials) as [Sum],
COUNT(*) over () [TotalCount] ------- total count here
from Bills b
inner join UserProfiles p on b.PayerUserName=p.UserName
where b.Successful=1
group by p.UserName, p.FirstName + ' ' + p.LastName
回答by madvora
If you have a unique column name in there, you can count that. For example I'm assuming UserName is unique here.
如果你有一个唯一的列名，你可以计算它。例如，我假设 UserName 在这里是唯一的。
select count(query.UserName) from (
select p.UserName, p.FirstName + ' ' + p.LastName as [FullName]
,count(b.billid) as [Count], sum(b.PercentRials) as [Sum] from Bills b
inner join UserProfiles p on b.PayerUserName=p.UserName
where b.Successful=1
group by p.UserName, p.FirstName + ' ' + p.LastName) as query
回答by Alexander Bogatyrev
COUNT() - aggregate function
COUNT() - 聚合函数
SELECT SQL_CALC_FOUND_ROWS * FROM ...; SELECT FOUND_ROWS(); // next query !!!!
SELECT SQL_CALC_FOUND_ROWS * FROM ...; 选择 FOUND_ROWS(); // 下一个查询！！！
