sql 分组查询没有记录的组不显示时设置为0

自己百度了半天总结的精华废话不多说直接上代码SELECT * FROM USER正常分组的结果SELECT username,COUNT(*) AS Number FROM USER WHERE use_status='2' GROUP BY username方法1SELECT t.username,SUM(t.Number) AS Number FROM(SELECT username,COUN

刘昌明843

4714人浏览 · 2022-01-18 21:37:13

刘昌明843 · 2022-01-18 21:37:13 发布

自己百度了半天总结的精华

废话不多说直接上代码

SELECT * FROM USER

正常分组的结果

SELECT username,COUNT(*) AS Number FROM USER WHERE use_status='2' GROUP BY username

方法1

SELECT t.username,SUM(t.Number) AS Number FROM
(SELECT username,COUNT(*) AS Number FROM USER WHERE use_status='2' GROUP BY username
UNION ALL
SELECT username,0 FROM USER GROUP BY username) AS t GROUP BY t.username

方法2

SELECT username,COUNT(CASE WHEN use_status='2' THEN use_status ELSE NULL END) AS use_status FROM USER GROUP BY username

惊喜

最后的结果