我想计算表中记录的总数,以及匹配特定条件的记录总数.我可以通过两个单独的查询执行这些操作
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};
有没有办法将这些组合成一个查询,以便我在一行中得到两个字段？
SELECT {something} AS TotalCount,{something else} AS QualifiedCount
FROM MyTable {possible JOIN(s)} WHERE {some conditions}
如果没有,我可以发出两个查询并将它们包装在一个事务中,这样它们就是一致的,但我希望能用一个查询.
编辑：我最关心原子性;如果有两个子SELECT语句需要,只要有来自某个地方的INSERT,它就不会使两个响应不一致.
编辑2：CASE答案是有帮助的,但在我的具体实例中,条件可能包括与另一个表的JOIN(忘了在我的原始帖子中提到,抱歉)所以我猜这种方法不起作用.
解决方法
一种方法是将表加入自身：
select
count(*) as TotalCount,count(s.id) as QualifiedCount
from
MyTable a
left join
MyTable s on s.id = a.id and {some conditions}
select
(select count(*) from Mytable) as TotalCount,(select count(*) from Mytable where {some conditions}) as QualifiedCount
或者你可以把条件放在一个案例中：
select
count(*) as TotalCount,sum(case when {some conditions} then 1 else 0 end) as QualifiedCount
from
MyTable
有关：
