MySQL中在含有GROUP BY的SELECT语句中显示COUNT()为0的结果

最新推荐文章于 2024-12-29 16:00:13 发布

原创最新推荐文章于 2024-12-29 16:00:13 发布 · 5k 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#MySQL #数据库 #group by #count #0

收录于

Java自学之路专栏收录该内容

28 篇文章

订阅专栏

本文通过具体的SQL查询示例，展示了如何使用MySQL的左连接(left join)结合分组(group by)来处理两个表之间的数据关联及统计。示例中涉及两张表t1和t2的数据关联与计数。

mysql> select * from t1;
+-------+------+
| level | name |
+-------+------+
|     1 | a    |
|     1 | b    |
|     1 | c    |
|     2 | a    |
|     2 | b    |
|     4 | a    |
|     5 | b    |
+-------+------+
7 rows in set (0.00 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.02 sec)

mysql> select t2.id as level,count(case when name is not null then 1 end) as count
    ->   from t2 left join t1 on t2.id = t1.level
    -> group by t2.id;
+-------+-------+
| level | count |
+-------+-------+
|     1 |     3 |
|     2 |     2 |
|     3 |     0 |
|     4 |     1 |
|     5 |     1 |
+-------+-------+
5 rows in set (0.00 sec)