两个 SQL COUNT() 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/888731/
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
two SQL COUNT() queries?
提问by Jason S
I want to count both the total # of records in a table, and the total # of records that match certain conditions. I can do these with two separate queries:
我想计算一个表中的记录总数，以及符合特定条件的记录总数。我可以用两个单独的查询来做这些：
SELECT COUNT(*) AS TotalCount FROM MyTable;
SELECT COUNT(*) AS QualifiedCount FROM MyTable
{possible JOIN(s) as well e.g. JOIN MyOtherTable mot ON MyTable.id=mot.id}
WHERE {conditions};
Is there a way to combine these into one query so that I get two fields in one row?
有没有办法将这些组合成一个查询，以便我在一行中得到两个字段？
SELECT {something} AS TotalCount,
{something else} AS QualifiedCount
FROM MyTable {possible JOIN(s)} WHERE {some conditions}
If not, I can issue two queries and wrap them in a transaction so they are consistent, but I was hoping to do it with one.
如果没有，我可以发出两个查询并将它们包装在一个事务中，以便它们保持一致，但我希望用一个来完成。
edit: I'm most concerned about atomicity; if there are two sub-SELECT statements needed that's OK as long as if there's an INSERT coming from somewhere it doesn't make the two responses inconsistent.
编辑：我最关心原子性；如果需要两个子 SELECT 语句，只要有来自某个地方的 INSERT，它就不会使两个响应不一致。
edit 2: The CASE answers are helpful but in my specific instance, the conditions may include a JOIN with another table (forgot to mention that in my original post, sorry) so I'm guessing that approach won't work.
编辑 2：CASE 答案很有帮助，但在我的特定情况下，条件可能包括与另一个表的 JOIN（忘记在我原来的帖子中提到这一点，抱歉）所以我猜这种方法行不通。
回答by Guffa
One way is to join the table against itself:
一种方法是加入表格反对自己：
select
count(*) as TotalCount,
count(s.id) as QualifiedCount
from
MyTable a
left join
MyTable s on s.id = a.id and {some conditions}
Another way is to use subqueries:
另一种方法是使用子查询：
select
(select count(*) from Mytable) as TotalCount,
(select count(*) from Mytable where {some conditions}) as QualifiedCount
Or you can put the conditions in a case:
或者您可以将条件放在一个案例中：
select
count(*) as TotalCount,
sum(case when {some conditions} then 1 else 0 end) as QualifiedCount
from
MyTable
Related:
有关的：
回答by Andomar
In Sql Server or MySQL, you can do that with a CASE statement:
在 Sql Server 或 MySQL 中，您可以使用 CASE 语句执行此操作：
select
count(*) as TotalCount,
sum(case when {conditions} then 1 else 0 end) as QualifiedCount
from MyTable
Edit: This also works if you use a JOIN in the condition:
编辑：如果您在条件中使用 JOIN，这也适用：
select
count(*) as TotalCount,
sum(case when {conditions} then 1 else 0 end) as QualifiedCount
from MyTable t
left join MyChair c on c.TableId = t.Id
group by t.id, t.[othercolums]
The GROUP BY is there to ensure you only find one row from the main table.
GROUP BY 用于确保您只能从主表中找到一行。
回答by Middletone
if you are just counting rows you could just use nested queries.
如果您只是计算行数，则可以使用嵌套查询。
select
(SELECT COUNT(*) AS TotalCount FROM MyTable) as a,
(SELECT COUNT(*) AS QualifiedCount FROM MyTable WHERE {conditions}) as b
回答by Ryu S.
In Oracle SQL Developer I had to add a * FROM in my select, or else i was getting a syntax error:
在 Oracle SQL Developer 中，我必须在我的选择中添加一个 * FROM，否则我会收到语法错误：
select * FROM
(select COUNT(*) as foo FROM TABLE1),
(select COUNT(*) as boo FROM TABLE2);
回答by Craig Lewis
MySQL doesn't count NULLs, so this should work too:
MySQL 不计算 NULL，所以这也应该有效：
SELECT count(*) AS TotalCount,
count( if( field = value, field, null)) AS QualifiedCount
FROM MyTable {possible JOIN(s)} WHERE {some conditions}
That works well if the QuailifiedCount field comes from a LEFT JOIN, and you only care if it exists. To get the number of users, and the number of users that have filled in their address:
如果 QuailifiedCount 字段来自 LEFT JOIN，则效果很好，并且您只关心它是否存在。获取用户数量，以及填写地址的用户数量：
SELECT count( user.id) as NumUsers, count( address.id) as NumAddresses
FROM Users
LEFT JOIN Address on User.address_id = Address.id;
