自引用表递归聚合：各层级部门含子部门平均薪资查询求助

我完全懂你这种卡在递归聚合的感觉——第一次用自引用表做层级统计时，很容易只抓到直接子部门的数据，没法覆盖所有嵌套层级。咱们结合常见的数据库架构，一步步把这个问题解决掉。

先明确假设的表结构 #

首先假设你有两张核心表：

• departments（部门表，自引用结构）：
  • id：部门唯一ID
  • parent_id：父部门ID（顶级部门为NULL）
  • name：部门名称
• employees（员工表）：
  • id：员工ID
  • department_id：所属部门ID
  • salary：员工薪资

核心思路：用递归CTE展开所有层级 #

递归CTE（Common Table Expression）是解决这类层级问题的关键，它能帮你把每个部门及其所有子部门、孙部门都展开关联起来，之后再做聚合统计就简单了。

完整SQL示例 #

-- 递归CTE：展开每个部门的所有下属层级，同时保留当前统计的根部门
WITH RECURSIVE department_hierarchy AS (
    -- 锚点成员：所有部门先把自己作为统计根节点
    SELECT 
        id AS department_id,
        id AS root_department_id,
        name AS root_department_name
    FROM departments

    UNION ALL

    -- 递归成员：关联子部门，把每个子部门归到它的所有父级根节点下
    SELECT 
        d.id AS department_id,
        dh.root_department_id,
        dh.root_department_name
    FROM departments d
    JOIN department_hierarchy dh ON d.parent_id = dh.department_id
)

-- 基于展开的层级数据，统计每个根部门的平均薪资（含所有下属）
SELECT 
    root_department_id AS department_id,
    root_department_name AS department_name,
    -- 处理无员工的情况，返回0而不是NULL
    COALESCE(AVG(e.salary), 0) AS average_salary
FROM department_hierarchy dh
LEFT JOIN employees e ON dh.department_id = e.department_id
GROUP BY root_department_id, root_department_name
ORDER BY root_department_id;

代码逻辑拆解 #

1. 递归CTE部分：

   • 锚点成员：先把所有部门都列出来，每个部门初始作为自己的统计根节点。
   • 递归成员：不断关联子部门，把每个子部门和它的父级根节点绑定——这样不管嵌套多少层，所有子部门都会被归到最上层的父部门（以及中间层级的父部门）下。

2. 聚合统计部分：

   • 通过LEFT JOIN关联员工表，确保没有员工的部门也能被统计到。
   • 用GROUP BY按根部门分组，计算所有关联部门（当前部门+所有下属）的薪资平均值。
   • COALESCE函数用来处理空值，避免没有员工的部门返回NULL。

你之前可能踩的坑 #

很多人第一次尝试时，只会用一次JOIN关联直接子部门，或者递归CTE只做了单层关联，没有把所有后代层级都纳入。这个方案的核心就是通过递归把整个部门树完全展开，让每个父部门能“看到”所有层级的子部门员工数据。

不同数据库的小差异 #

• MySQL 8.0+ 和 PostgreSQL：需要写WITH RECURSIVE
• SQL Server：不需要RECURSIVE关键词，直接用WITH即可

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