SQL实现：统计满足条件的记录数并返回含零值的分组结果

问题背景 #

需要统计每位manager手下姓名为John的员工数量，要求包含无符合条件员工的manager（对应计数为0）。现有数据表employees结构及数据如下：

期望输出结果：

原SQL语句使用WHERE子句筛选，无法返回计数为0的manager：

SELECT manager_id, COUNT(*)
FROM employees
WHERE name = 'John'
GROUP BY manager_id

问题分析 #

原SQL先通过WHERE name = 'John'过滤掉了所有非John的员工，导致没有下属叫John的manager（比如manager_ID=2）根本不会出现在分组结果中，自然无法得到计数为0的记录。

正确实现方案 #

方案1：使用条件计数（CASE表达式） #

无需额外连接操作，直接在COUNT中加入条件判断，仅统计name为John的员工：

SELECT 
    manager_id,
    COUNT(CASE WHEN name = 'John' THEN 1 END) AS count
FROM employees
GROUP BY manager_id

原理：CASE WHEN仅当name为John时返回1，否则返回NULL；COUNT()函数会忽略NULL值，这样就能得到每个manager手下John的数量，无符合条件员工时计数为0。

方案2：先获取所有manager，再左连接统计结果 #

先提取所有唯一的manager_ID，再左连接筛选出John员工的统计数据，确保所有manager都被包含：

SELECT 
    m.manager_id,
    COALESCE(j.count, 0) AS count
FROM (SELECT DISTINCT manager_id FROM employees) m
LEFT JOIN (
    SELECT manager_id, COUNT(*) AS count
    FROM employees
    WHERE name = 'John'
    GROUP BY manager_id
) j ON m.manager_id = j.manager_id

原理：子查询m先获取所有存在的manager_ID，左连接子查询j（原SQL的结果），对没有匹配的manager，用COALESCE将NULL替换为0。

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