问题:
对于同一个查询中存在多个count(*),想要将每个count(*)都为0的行去掉。
select a.name ,(select count(*) from b where b.id = a.bid) countB,
(select count(*) from c where c.id = a.cid) countC,
(select count(*) from d where d.id = a.did) countD
from a
结果是:
解决:
在原查询外面再嵌套一层查询
select t.* from (
select a.name ,
(select count(*) from b where b.id = a.bid) countB,
(select count(*) from c where c.id = a.cid) countC,
(select count(*) from d where d.id = a.did) countD
from a) t
where (t.countB+t.countC+t.countD)>0
这样的结果就可以把三者都为0的行去掉了