刷 LeetCode SQL 题的时候碰到 3482 这道“分析组织层级”（Analyze Organization Hierarchy），起初我以为是普通的聚合统计，结果仔细一看，表里manager_id指向employee_id，典型到不行的自引用结构，而且组织深度不确定——这意味着不能用固定次数的JOIN去解。它真正想考的是 SQL 里最容易被忽略、一上真实业务就容易写崩的知识点：递归查询。
这类需求在真实工作中太常见了，员工组织架构、商品类目树、BOM 物料清单、论坛评论楼，全是同一套“父子关系”的数据模型。所以这道题刷完之后我专门写了这篇复盘，把递归 CTE 的写法、边界条件、性能注意点、以及不同数据库的方言差异都梳理了一遍。适合正在准备数据岗面试的人、刷 SQL 题卡住的同学，以及工作中被组织树搞到头大的数据分析师。提前说一句：这篇不是单纯贴答案，而是把每一步为什么这么写讲清楚，如果你只想要代码，直接拉到第 3 节。
1. 题目核心拆解：组织层级分析到底在考什么
1.1 表结构与查询目标
题目给出的表结构大致如下：
| 列名 | 类型 | 含义 |
|---|---|---|
| employee_id | int | 员工ID，主键 |
| name | varchar | 员工姓名 |
| salary | int | 员工工资 |
| manager_id | int | 该员工的直属经理ID，指向 employee_id |
逻辑上这是一张自引用表：经理也是员工，自己的employee_id被别人当作manager_id引用。manager_id为NULL的人，就是公司最高层，也就是 CEO。
输出需要按组织层级统计两个指标：
- 层级编号：CEO 记为第 1 层，CEO 的直接下属记为第 2 层，以此类推。
- 每个层级的员工总数。
- 每个层级的平均工资，保留两位小数。
输出大概长这样：
| level | employee_count | avg_salary |
|---|---|---|
| 1 | 1 | 100000.00 |
| 2 | 3 | 80000.00 |
| 3 | 8 | 60000.00 |
这个需求放到真实业务里特别常见：HR 看各层级人力分布、财务算每个汇报层级的薪酬成本、运营统计各级管理幅度。说白了，就是把一棵树从根到叶子逐层展开，然后按层分组聚合。
1.2 题目想考察的核心能力
这道题摆在 LeetCode 的 Hard 区间，核心考察三点。
第一，是否能识别出这是一个树形结构。很多人一看到manager_id就懵了，不知道该从哪下手，甚至有人想用GROUP BY manager_id来硬算层级，那就完全跑偏了。实际上这张表本身就是一棵组织树，树的根是manager_id IS NULL的员工。
第二，是否掌握递归 CTE（WITH RECURSIVE）。SQL 处理树形结构最标准的手段就是递归 CTE，先找一个锚点（根节点），然后反复执行同一个查询去“往下挂”子节点，直到找不到新节点为止。LeetCode 的 SQL 题直接考递归的其实不算多，3482 就是其中之一。
第三，是否能正确处理聚合与边界。拿到递归结果之后，按level分组，COUNT(*)数人数，AVG(salary)算均值，最后ROUND保留两位小数。看起来都是基础语法，但组合起来恰好覆盖了“先展开树、再分组聚合”的完整链路。
1.3 一个容易忽略的输出细节
注意输出里的avg_salary是带两位小数的，所以AVG(salary)之后必须接ROUND(..., 2)。如果只是AVG(salary)，在整数除法环境下可能得到不带小数的值，甚至在 MySQL 中如果 salary 是整数类型，AVG的结果可能是不够精确的 decimal，也需要显式控制精度。
还有排序：一般要求按层级升序返回，也就是ORDER BY level。如果题目里额外要求“层级相同按人数排序”，就再补一个ORDER BY level, employee_count。我们下面写解法的时候把排序也一起做了，方便各版本数据库直接套用。
2. 解题思路演进：从多层自连接到递归 CTE
2.1 自连接为什么撑不住
很多人的第一反应是自连接。如果公司只有 3 层，比如 CEO -> 总监 -> 经理，那确实可以：
SELECT ... FROM employees e1 LEFT JOIN employees e2 ON e1.employee_id = e2.manager_id LEFT JOIN employees e3 ON e2.employee_id = e3.manager_id但问题来了：公司组织可能有 5 层、8 层、甚至 12 层。每多一层，SQL 就要多写一个JOIN，而且事先根本写不出来——因为你不知道这棵树有多深。就算你知道最深 10 层，写 9 个LEFT JOIN这样的 SQL 也会变成一场灾难，性能和可读性双双崩掉。
这是一个典型的“层级深度不确定”问题。你需要的不是“手动指定连接几次”，而是一个“自动重复执行”的机制。
2.2 递归 CTE 的运行机制
递归 CTE 的语法在大多数数据库中长这样：
WITH RECURSIVE cte_name AS ( -- 锚点成员：初始结果集 SELECT ... FROM ... WHERE ... UNION ALL -- 递归成员：引用自身 SELECT ... FROM cte_name ... ) SELECT * FROM cte_name;它的执行过程可以这样理解：
- 第一步，执行锚点查询，得到第一批结果，这批结果就是递归的起点。
- 第二步，用当前结果集去执行递归查询，得到第二批结果。
- 第三步，用第二批结果再执行递归查询，得到第三批，如此反复。
- 什么时候停止？当某一步执行递归查询后结果集为空，迭代自然终止。
打个生活化的比方：你往一个本子上写“去问 A 的下属是谁”，找到后把名字记下来，再对每个新名字问“你的下属是谁”，一直问到没有人再报出新名字为止。递归 CTE 干的就是这件事。
这里用UNION ALL而不是UNION也有讲究。UNION ALL保留所有重复行，UNION会去重。在树形结构中，一个员工理论上只会在一个层级出现一次，如果数据没问题，二者结果一样；但UNION去做重需要额外排序和比较，性能更差。所以标准写法用UNION ALL。
2.3 终止条件：不是写出来的，是数据决定的
很多新手第一次接触递归 CTE 都会问：要不要写终止条件？
答案是：正常情况下不需要显式写。递归 CTE 的迭代停止条件是“递归成员的结果集为空”。也就是说，当遍历到叶子节点（没有下属），下一轮递归查不到数据，自动结束。
但有一个人为的预期终止条件会在极端情况下起作用：数据库通常还设置了递归深度上限。比如 MySQL 有个cte_max_recursion_depth参数，默认值是 1000。如果递归超过这个深度，MySQL 会直接报错。为什么要设置上限？因为如果表里的数据存在环——比如 A 的经理是 B，B 的经理又是 A——递归查询就会陷入无限循环，不断产出新行，最终拖垮数据库。这个参数相当于安全阀，我们后面在踩坑部分再展开。
3. 完整 SQL 实现与逐段解析
3.1 第一步：写出锚点成员，确定顶层
组织树的顶层就是没有经理的人，也就是manager_id IS NULL的员工。这一层定为level = 1：
SELECT employee_id, salary, 1 AS level FROM Employees WHERE manager_id IS NULL注意这里选择了employee_id和salary，因为最终统计只需要这两列。如果你把name也带进来，递归过程中会带着多余字段一起传递，数据量大时白白浪费内存。这是一种实用的小优化思路：递归 CTE 里只保留后续需要的字段。
3.2 第二步：写出递归成员，逐层向下挂子节点
递归成员要引用 CTE 自身，把每个员工的直属下级找出来，并且层数加 1：
SELECT e.employee_id, e.salary, oh.level + 1 FROM Employees e INNER JOIN OrgHierarchy oh ON e.manager_id = oh.employee_id这里INNER JOIN的意思是：只有找到了对应的经理（这个经理在已展开的层级中存在），员工才会被纳入下一层。第一次迭代时，OrgHierarchy里只有 CEO，所以这一轮找到的就是 CEO 的直接下属，level 变成 2；第二轮找到的就是他们的下属，level 变成 3，以此类推。
注意，这个递归成员必须和锚点成员的列结构保持一致：employee_id、salary、level。这也是递归 CTE 的硬性要求——所有成员返回的列数和列类型要一致。
3.3 第三步：把两步合并成完整递归 CTE
WITH RECURSIVE OrgHierarchy AS ( SELECT employee_id, salary, 1 AS level FROM Employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.salary, oh.level + 1 FROM Employees e INNER JOIN OrgHierarchy oh ON e.manager_id = oh.employee_id ) SELECT * FROM OrgHierarchy;执行到这一步，你得到的是一个平面化的结果集：每个员工一行，附带他所在的层级。举个例子，假设公司数据是这样的：
| employee_id | name | salary | manager_id |
|---|---|---|---|
| 1 | CEO | 100000 | NULL |
| 2 | 总监A | 80000 | 1 |
| 3 | 总监B | 90000 | 1 |
| 4 | 经理C | 60000 | 2 |
| 5 | 经理D | 55000 | 2 |
| 6 | 经理E | 65000 | 3 |
递归展开的结果就是：
| employee_id | salary | level |
|---|---|---|
| 1 | 100000 | 1 |
| 2 | 80000 | 2 |
| 3 | 90000 | 2 |
| 4 | 60000 | 3 |
| 5 | 55000 | 3 |
| 6 | 65000 | 3 |
到了这一步，原来的树已经被“拍平”成一张带层级的明细表。剩下的问题就变成最简单的分组聚合了。
3.4 第四步：按层级分组聚合，得到最终结果
SELECT level, COUNT(*) AS employee_count, ROUND(AVG(salary), 2) AS avg_salary FROM OrgHierarchy GROUP BY level ORDER BY level;COUNT(*)统计每个层级的人数，AVG(salary)计算平均工资，ROUND(..., 2)保留两位小数。因为题目通常要求按层级升序返回，最后加上ORDER BY level。
如果题目的输出文件中，某层的平均工资恰好是一个整数，比如 80000，ROUND之后在 MySQL 中会显示为80000.00，这也符合保留两位小数的要求。
3.5 完整代码（兼容多种数据库）
把上面四步拼起来就是最终的解法：
WITH RECURSIVE OrgHierarchy AS ( SELECT employee_id, salary, 1 AS level FROM Employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.salary, oh.level + 1 FROM Employees e INNER JOIN OrgHierarchy oh ON e.manager_id = oh.employee_id ) SELECT level, COUNT(*) AS employee_count, ROUND(AVG(salary), 2) AS avg_salary FROM OrgHierarchy GROUP BY level ORDER BY level;这里给几个数据库的兼容性提示：
- MySQL 8.0+：完全支持，直接跑。
- PostgreSQL：完全支持，直接跑。
- SQL Server：语法上不需要
RECURSIVE关键字，写成WITH OrgHierarchy AS (...)即可，其余不变。 - SQLite：3.8.3 及以上支持
WITH RECURSIVE。 - Oracle：除了支持
WITH RECURSIVE外，也可以使用传统的CONNECT BY PRIOR写法，后面第 5 节我们会提到。
如果你用的是 MySQL 5.7 或更低版本，那就没有递归 CTE 了，这种题基本只能靠临时表和循环存储过程去硬解。从侧面也说明，处理树形结构在旧版 MySQL 中是多么痛苦。
4. 实操中遇到的坑与排查技巧
4.1 数据成环导致的无限递归
递归 CTE 最大的坑就是数据环。比如员工 A 的manager_id指向 B，同时 B 的manager_id又指向 A，递归就会在 A、B 之间反复横跳。每次迭代都能生成新的行，如果数据库没有保护机制，查询就会一直跑下去，直到资源耗尽。
MySQL 的处理方式是通过系统变量cte_max_recursion_depth限制递归深度，默认值是 1000。超过这个深度，直接报错：
Recursive query aborted after 1001 iterations. Try increasing the value of the 'cte_max_recursion_depth' session variable.这个报错信息其实就是告诉你：要么数据有环，要么组织真的深得离谱。在真实业务中，出现这个报错的第一反应应该是去查数据质量，而不是盲目调大参数。
如何防范？最实际的办法是在数据入表时就做校验。比如写一个触发器或定时任务，检查是否存在manager_id形成闭环的情况；或者在递归 CTE 中增加一个路径字段，记录已经访问过的员工ID列表，遇到重复就终止。不过后者实现起来比较复杂，LeetCode 这种场景用不到，真实业务中我们一般还是从源头治理数据。
4.2 多个最高层管理者怎么办
题目通常默认只有一个 CEO，也就是一行manager_id IS NULL。但真实数据不是这样的：有的系统里，创始人和联合创始人都在顶层，或者因为离职交接导致某些人的manager_id变成NULL。这时候递归 CTE 会输出多个level = 1的行，统计结果中也会出现多行层级相同的记录。
如果业务要求只能有一个顶层节点，得在锚点部分做筛选。常见的做法是：按入职时间或员工ID排序，取最早的那个作为顶层节点。比如：
SELECT employee_id, salary, 1 AS level FROM ( SELECT employee_id, salary, ROW_NUMBER() OVER (ORDER BY hire_date ASC) AS rn FROM Employees WHERE manager_id IS NULL ) t WHERE rn = 1用窗口函数把manager_id IS NULL的人排序，只保留第一个作为根，这样组织树就只有一个顶点了。这个思路在真实的口径梳理中很有用。
4.3 工资字段为 NULL 时的统计陷阱
AVG(salary)会自动忽略NULL值。如果某一层所有员工的 salary 都是NULL，AVG返回的是NULL，ROUND(NULL, 2)还是NULL。这在 LeetCode 的测试用例里大概率不会出现，但在处理真实业务数据时很常见——有些离职员工的工资在历史快照里就是缺失的。
稳妥的写法是加一层兜底：
ROUND(COALESCE(AVG(salary), 0), 2) AS avg_salary这样即使整层工资缺失，输出也是0.00而不是NULL，避免下游报表解析出错。当然，如果业务上认为缺失不应该算 0，而是应该按排除缺失后的实际均值展示，那就不用 COALESCE，见仁见智。
4.4 递归 CTE 的性能问题
很多人担心递归 CTE 在大数据量下跑不动。这个担忧部分有道理，部分属于误解。
递归 CTE 的执行方式是迭代式的：每一轮迭代把当前结果集拿去 JOIN。如果组织树有 10 万员工，但层级只有 6 层，那递归轮数只有 6 轮，每轮的数据量按树的宽度分布，一般不会太大，整体性能是可以接受的。但如果组织树是“深而窄”的结构，比如 100 层、每层只有 1-2 个人，那么递归轮数会很多，每轮虽然数据量小，但查询次数多，整体耗时也会被拉长。
优化建议很直接：
- 在
manager_id上建索引。递归 JOIN 的核心条件就是e.manager_id = oh.employee_id，如果manager_id没有索引，每一轮迭代都是全表扫描，代价极高。真实业务表中这几乎是必须的索引。 - 递归成员中只保留必要字段。前面已经提过，不要把
name、hire_date这些无关字段带到递归里，减少每轮迭代的传输和内存消耗。 - 尽量在递归完成后再过滤和聚合。不要在递归成员内部做
WHERE salary > 50000之类的过滤，除非你确定过滤条件不会影响树的展开——如果某个经理被过滤掉，他的所有下级都会丢失，层级就断了。
4.5 不同数据库的递归写法差异
我实际测试过多个数据库，把差异列在下面，方便读者对照：
| 数据库 | 递归关键字 | 注意事项 |
|---|---|---|
| MySQL 8.0+ | WITH RECURSIVE | 有深度限制，默认 1000 |
| PostgreSQL | WITH RECURSIVE | 支持得很好，递归深度默认不限制 |
| SQL Server | WITH cte_name AS | 不需要RECURSIVE关键字 |
| SQLite | WITH RECURSIVE | 3.8.3 及以上支持 |
| Oracle | CONNECT BY PRIOR/ 递归CTE | 传统写法是CONNECT BY PRIOR |
LeetCode 上默认支持的数据库引擎通常包括 MySQL、MS SQL Server 和 PostgreSQL，所以上文的写法在 MySQL 里测最稳。SQL Server 用户把RECURSIVE去掉即可，其他部分一模一样。
5. 题目变体与扩展思考
5.1 变体一：求每个层级的工资中位数
如果题目再变态一点，不要求平均工资而是中位数，思路就要换成窗口函数。先给每个层级内部的员工按工资排序，然后取中间位置：
WITH Ranked AS ( SELECT level, salary, ROW_NUMBER() OVER (PARTITION BY level ORDER BY salary) AS rn, COUNT(*) OVER (PARTITION BY level) AS cnt FROM OrgHierarchy ) SELECT level, AVG(salary) AS median_salary FROM Ranked WHERE rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2)) GROUP BY level;这个变体在数据分析面试中出现频率很高，因为它把“递归CTE拼树 + 窗口函数统计”两个知识点串在一起，考察面更综合。
5.2 变体二：输出每个员工的完整汇报链
有时候业务想看“某员工上面有谁”，这就需要沿着manager_id一路向上走。实现方法和向下展开类似，只是递归的方向反过来了——锚点是目标员工，递归成员是找他的经理：
WITH RECURSIVE Chain AS ( SELECT employee_id, manager_id, name, 1 AS depth FROM Employees WHERE employee_id = 某员工ID UNION ALL SELECT e.employee_id, e.manager_id, e.name, c.depth + 1 FROM Employees e INNER JOIN Chain c ON e.employee_id = c.manager_id ) SELECT * FROM Chain;这种“向上回溯”的查询在做权限审批流、数据归属追溯时非常有用。
5.3 变体三：按部门维度嵌套统计
真正企业里的组织架构通常比这道题复杂得多，比如员工除了manager_id之外还有department_id。如果把层级统计和部门维度组合起来，可以分析“每个部门在每个层级的人数分布”，这就是典型的人力结构分析。做法不复杂，在递归 CTE 展开后，GROUP BY department_id, level即可。需要保证递归时带上 department_id 字段。
5.4 现实场景的延伸：不止组织树能这么搞
递归 CTE 的应用范围远不止组织架构。我实际用这套思路处理过：
- 商品类目树：统计每个层级有多少个叶子商品 SKU。
- BOM 物料清单：展开一个成品的所有原材料，计算多层用量。
- 评论楼中楼：把回复关系展开成树，统计每一层的回复数量。
- 权限继承树：根据角色继承关系，推导用户最终拥有的权限集合。
这些场景的 SQL 结构都和 3482 题一模一样：一张自引用表 + 一个根节点 + 递归 CTE + 分组聚合。把这一题吃透，等于掌握了 SQL 处理所有树形结构的通用模板。
我在实际项目里踩过递归 CTE 的环，从那之后养成了习惯：凡是写递归查询，先看一眼数据里有没有循环引用的可能，并且每次在测试环境跑一个LIMIT 1000的版本确认结果符合预期再放开。LeetCode 上的题目数据通常很干净，但真实生产环境才是真正考验基本功的地方。如果你也想把这套思路用到工作中，建议先从组织架构这种“低风险”的数据开始练手，跑顺了再往更复杂的树上靠，这会让你对 SQL 的理解提升一个台阶。
