SQL GroupBy查询中实现统计结果包含零计数的问题咨询

问题根因 #

你写的SQL没有返回零计数记录，主要是三个错误导致的：

• 左连接的主从表顺序错误：当前你以Reference作为左表，左连接只会保留Reference中存在的记录，不会保留你需要的全部应用列表记录。要输出所有指定应用的统计结果，必须把应用所在的docapplication作为左表。
• 右表过滤条件写在WHERE子句中：左连接匹配不到的记录，右表docapplication的所有字段都会是NULL，你在WHERE中写d.appprofile in (xxx)会直接过滤掉所有NULL值的行，本质上把左连接变成了内连接，自然不会出现零计数的记录。
• 计数函数使用错误：count(*)会统计所有返回行，哪怕关联不到Reference的记录也会计数为1，无法得到0的统计结果。

修正后的SQL #

select count(r.did) as Total_Count, d.appprofile as Application
from docapplication d 
LEFT OUTER JOIN Reference r ON 
d.did = r.did 
AND r.CREATEDATE >= '1-Jul-21 12.00.00.000000000 AM' 
AND r.CREATEDATE <= '31-JUL-21 11.59.00.000000000 PM'
where d.appprofile in ('EbsApp_0','EbsApp_2','EbsApp_3','EbsApp_4','EbsApp_5','EbsApp_6','EbsApp_8','EbsApp_9','EbsApp_10','EbsApp_11','EbsApp_12','EBS_DOC1','EBS_DOC2')
group by d.appprofile
order by d.appprofile;

改动说明 #

• 调换了主从表顺序，以docapplication作为左表，确保所有指定的应用都会保留在结果集中
• 把Reference表的时间过滤条件从WHERE子句移到了JOIN的ON子句中，这样只会过滤Reference中符合时间要求的记录，不会过滤掉匹配不到的应用记录
• 把count(*)改成了count(r.did)，关联不到Reference的记录r.did为NULL，COUNT统计时会忽略NULL值，最终返回0符合需求

如果你的docapplication表中不存在部分你指定的appprofile记录，可以先手动构造所有需要统计的应用列表作为主表，确保所有应用都出现在结果中：

with app_list as (
    select 'EbsApp_0' as appprofile union all
    select 'EbsApp_2' union all
    select 'EbsApp_3' union all
    select 'EbsApp_4' union all
    select 'EbsApp_5' union all
    select 'EbsApp_6' union all
    select 'EbsApp_8' union all
    select 'EbsApp_9' union all
    select 'EbsApp_10' union all
    select 'EbsApp_11' union all
    select 'EbsApp_12' union all
    select 'EBS_DOC1' union all
    select 'EBS_DOC2'
)
select count(r.did) as Total_Count, a.appprofile as Application
from app_list a
left join docapplication d on a.appprofile = d.appprofile
LEFT OUTER JOIN Reference r ON 
d.did = r.did 
AND r.CREATEDATE >= '1-Jul-21 12.00.00.000000000 AM' 
AND r.CREATEDATE <= '31-JUL-21 11.59.00.000000000 PM'
group by a.appprofile
order by a.appprofile;

内容的提问来源于stack exchange，提问作者Cris_new