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

最新推荐文章于 2024-08-27 14:15:18 发布

原创最新推荐文章于 2024-08-27 14:15:18 发布 · 4.3k 阅读

2 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

8 篇文章

订阅专栏

本文通过一个具体的例子展示了如何使用SQL进行复杂的联表查询和聚合操作。包括如何创建表、插入数据，并逐步构建复杂的SQL查询语句，实现跨表的数据统计。

 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