sql 查询 group by查询count个数某记录不存在时显示0

最新推荐文章于 2024-09-12 09:56:34 发布

SURLIYA

最新推荐文章于 2024-09-12 09:56:34 发布

阅读量4.2k

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/SURLIYA/article/details/72539897

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

 create table t
 (
    id int,
    a int,
    b int
 )
  
 insert into t values(1,101,1);
 insert into t values(2,102,1);
 insert into t values(3,101,2);
 insert into t values(4,102,2);
 insert into t values(5,101,1);



 SELECT m.a,m.mb,n.nb from (select a.a,COUNT(c.b) As mb  from 
 (select distinct a from t) a   cross join 
 (select distinct b from t WHERE b=1) b
 left join t c on a.a=c.a and b.b=c.b
 Group by a.a,b.b ) m cross join 

(select a.a,COUNT(c.b) As nb  from 
 (select distinct a from t) a   cross join 
 (select distinct b from t WHERE b=2) b
 left join t c on a.a=c.a and b.b=c.b
 Group by a.a,b.b) n on m.a = n.a 


select a.a,COUNT(c.b) As mb  from 
 (select distinct a from t) a   cross join 
 (select distinct b from t WHERE b=1) b
 left join t c on a.a=c.a and b.b=c.b
 Group by a.a,b.b