如何通过连接同表添加含聚合值的Missing_Counts列？

需求 #

现有表Table1包含ID、Type、Persons字段，需要生成以下结果：

• 每个ID与Type的唯一组合
• Counts列：统计该组合的总记录数
• Missing_Counts列：统计该组合中Persons值为Null的记录数

现有输出（仅含总计数） #

Id       Type            Counts
EUR      Accounts        3600
GBP      Accounts        3673
EUR     Science          1724
CNY     Physics         1608
GBP     Physics         4437
CNY     Chemistry       5070
EUR     Chemistry       1499
GBP     Chemistry       33752
EUR     Math            5155

期望输出（增加缺失值计数） #

Id       Type            Counts  Missing_Counts
EUR      Accounts        3600     200
GBP      Accounts        3673
EUR     Science          1724      400
CNY     Physics         1608
GBP     Physics         4437
CNY     Chemistry       5070
EUR     Chemistry       1499      600
GBP     Chemistry       33752
EUR     Math            5155      800

原SQL存在的问题 #

你写的SQL有语法错误和冗余问题：

1. 语法顺序错误：GROUP BY应该放在JOIN之后，不能在FROM之后直接写GROUP BY再执行JOIN
2. 子查询缺少别名：LEFT JOIN的子查询必须指定别名，否则无法关联字段
3. 冗余的DISTINCT：GROUP BY Id, Type已经会返回唯一组合，不需要再加SELECT DISTINCT

修正方案 #

方案1：LEFT JOIN子查询（匹配你的初始思路） #

SELECT 
    p.Id, 
    p.Type, 
    p.Counts, 
    q.missing_count AS Missing_Counts
FROM (
    -- 先统计每个组合的总记录数
    SELECT 
        Id, 
        Type, 
        COUNT(*) AS Counts
    FROM Table1
    GROUP BY Id, Type
) p
LEFT JOIN (
    -- 统计每个组合中Persons为Null的记录数
    SELECT 
        Id, 
        Type, 
        COUNT(*) AS missing_count
    FROM Table1
    WHERE Persons IS NULL
    GROUP BY Id, Type
) q ON p.Id = q.Id AND p.Type = q.Type

方案2：条件聚合（更简洁高效，无需JOIN） #

推荐使用这种写法，仅需一次表扫描：

SELECT 
    Id, 
    Type, 
    COUNT(*) AS Counts,
    -- 仅对Persons为Null的记录计数，无符合条件时返回Null（匹配期望输出的空值显示）
    COUNT(CASE WHEN Persons IS NULL THEN 1 END) AS Missing_Counts
FROM Table1
GROUP BY Id, Type

补充说明 #

• 方案2中，COUNT(CASE ...)会自动忽略Null值，因此当组合没有Persons为Null的记录时，Missing_Counts列会显示Null，与期望输出一致
• 如果希望将Null显示为空字符串，可根据数据库语法调整为COALESCE(COUNT(CASE WHEN Persons IS NULL THEN 1 END), '') AS Missing_Counts

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