MariaDB中GROUP BY统计KYC类型时如何让缺失类型返回计数0

核心思路是先构造包含所有5种KYC类型的临时结果集，再左关联业务表统计数据，即可实现无数据的类型返回0的效果。

具体实现SQL #

SELECT 
    t.kyc_type,
    COUNT(k.kyc_type) AS count
FROM (
    -- 构造固定的全量KYC类型临时列表
    SELECT 'self' AS kyc_type
    UNION ALL SELECT 'shg'
    UNION ALL SELECT 'mfg'
    UNION ALL SELECT 'jlg'
    UNION ALL SELECT 'or'
) t
LEFT JOIN kyc_table k 
    ON t.kyc_type = k.kyc_type 
    AND YEAR(k.created_at) = 2020
GROUP BY t.kyc_type;

注意事项 #

• 时间筛选条件必须写在LEFT JOIN的ON子句中，不能写在WHERE子句里：如果写在WHERE中，左关联生成的无数据类型行的created_at为NULL，会被YEAR(created_at)=2020过滤，最终还是只会返回有数据的类型
• 统计计数使用COUNT(k.kyc_type)而非COUNT(*)：COUNT(*)会统计所有行（包括关联为空的行），COUNT(指定字段)只会统计该字段非空的行，无关联的行k.kyc_type为NULL，统计结果就是0，符合需求
• 如果5种KYC类型已经存储在独立的字典表中，可直接替换上述SQL中UNION ALL构造的临时表为字典表查询，写法更简洁。

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