如何通过两次自连接查询员工的直接与间接下属数量

原SQL存在的问题 #

• 使用INNER JOIN关联下属表，若员工没有直接/间接下属，对应记录会被直接过滤，统计结果缺失
• 别名引用错误：第二层关联时写的Employee1与上层定义的别名Employee_1不匹配
• COUNT语法存在错误，缺少右括号，且直接统计会因关联后数据膨胀导致计数重复
• 若某直接下属存在多个间接下属，会导致直接下属的统计结果重复计数

正确实现方案 #

方案1：左连接+去重计数（兼容所有支持标准SQL的数据库）

仅统计直接下属、二级间接下属时可以用这个方案，逻辑简单兼容性强：

SELECT
    e.EmployeeKey,
    -- 直接下属数量，去重避免间接下属关联导致的重复计数
    COUNT(DISTINCT e1.EmployeeKey) AS direct_subordinate_count,
    -- 间接下属（下属的下属）数量
    COUNT(DISTINCT e2.EmployeeKey) AS indirect_subordinate_count
FROM 
    Employee e
-- 左连接关联直接下属，无直接下属时也保留主表员工记录
LEFT JOIN Employee AS e1 
    ON e1.ParentEmployeeKey = e.EmployeeKey 
-- 左连接关联间接下属，无间接下属时也保留主表员工记录
LEFT JOIN Employee AS e2 
    ON e2.ParentEmployeeKey = e1.EmployeeKey
-- 替换为你需要的筛选条件，查询指定员工就指定EmployeeKey，查询所有员工则去掉该条件
WHERE
    e.EmployeeKey = '目标员工的Key'
GROUP BY 
    e.EmployeeKey

方案2：递归CTE实现（适用于MySQL8.0+、PostgreSQL、SQL Server等支持递归语法的数据库）

如果后续需要扩展统计更多层级的下属，推荐用这个方案，灵活性更高：

WITH RECURSIVE subordinate_hierarchy AS (
    -- 锚点：查询目标员工的直接下属，层级标记为1
    SELECT 
        EmployeeKey,
        ParentEmployeeKey,
        1 AS hierarchy_level
    FROM Employee
    WHERE ParentEmployeeKey = '目标员工的Key'
    UNION ALL
    -- 递归查询所有下级
    SELECT 
        e.EmployeeKey,
        e.ParentEmployeeKey,
        sh.hierarchy_level + 1 AS hierarchy_level
    FROM Employee e
    INNER JOIN subordinate_hierarchy sh 
        ON e.ParentEmployeeKey = sh.EmployeeKey
    -- 限制只查2级下属，需要统计更多层级调整该数值即可
    WHERE sh.hierarchy_level < 2
)
SELECT
    '目标员工的Key' AS EmployeeKey,
    SUM(CASE WHEN hierarchy_level = 1 THEN 1 ELSE 0 END) AS direct_subordinate_count,
    SUM(CASE WHEN hierarchy_level = 2 THEN 1 ELSE 0 END) AS indirect_subordinate_count
FROM subordinate_hierarchy

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