获取无上级主管下属员工薪资总和平均值的SQL查询求助

现有两张业务表：

• employee表：包含employee_id（员工ID）和supervisor_id（上级ID）字段
• salary表：包含employee_id和amount（薪资金额）字段

需求明确：针对无上级的主管（已知employee_id为7和8），计算其所有直接及间接下属员工的薪资总和的平均值（示例：主管7的下属为5、2、1、0；主管8的下属为6、4、3）。

用户尝试使用递归CTE实现，但未得到理想结果，当前代码如下：

WITH RECURSIVE supervisor_tree AS (
  SELECT e.id, e.supervisor_id, (SELECT f.amount FROM salary f WHERE f.employee_id= e.id) AS amount
  FROM employees e
  WHERE e.supervisor_id IS NULL -- only top-level supervisors
  UNION ALL
  SELECT e.id, e.supervisor_id, COALESCE(f.weight, 0) AS weight
  FROM employees e
  INNER JOIN salary f ON e.id = f.employee_id
  INNER JOIN supervisor_tree st ON e.supervisor_id = st.id
)
SELECT supervisor_id, weight 
FROM supervisor_tree;

原递归CTE存在三个核心问题：

• 递归分支错误引用了不存在的weight字段，应该使用salary表的amount
• 未正确追踪每个下属对应的顶级主管（无上级的主管）
• 缺少后续的聚合计算逻辑

以下是修正后的完整SQL代码：

WITH RECURSIVE employee_hierarchy AS (
    -- 起始节点：选中所有无上级的顶级主管，标记自身为顶级主管ID
    SELECT 
        e.employee_id, 
        e.supervisor_id, 
        e.employee_id AS top_supervisor_id
    FROM employee e
    WHERE e.supervisor_id IS NULL
    UNION ALL
    -- 递归遍历：为每个下属继承上级的顶级主管ID
    SELECT 
        e.employee_id, 
        e.supervisor_id, 
        eh.top_supervisor_id
    FROM employee e
    JOIN employee_hierarchy eh ON e.supervisor_id = eh.employee_id
),
supervisor_salary_agg AS (
    -- 按顶级主管分组，计算下属薪资总和与下属人数
    SELECT 
        eh.top_supervisor_id,
        SUM(s.amount) AS total_subordinate_salary,
        COUNT(eh.employee_id) AS subordinate_count
    FROM employee_hierarchy eh
    JOIN salary s ON eh.employee_id = s.employee_id
    -- 排除顶级主管自身，仅统计下属数据
    WHERE eh.employee_id != eh.top_supervisor_id
    GROUP BY eh.top_supervisor_id
)
-- 计算薪资总和的平均值
SELECT 
    top_supervisor_id AS supervisor_id,
    total_subordinate_salary / subordinate_count AS avg_total_subordinate_salary
FROM supervisor_salary_agg;

代码逻辑说明 #

• employee_hierarchy CTE：通过递归构建员工层级关系，为每个员工绑定其对应的顶级主管ID。起始部分筛选无上级的主管，递归部分将下属的顶级主管ID继承自直接上级的顶级主管ID，确保所有下属最终关联到正确的顶级主管。
• supervisor_salary_agg CTE：关联薪资表，按顶级主管分组，统计每个主管下属的薪资总和以及下属人数，同时排除主管自身的数据。
• 最终查询：用薪资总和除以下属人数，得到每个顶级主管下属薪资总和的平均值。

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