SQL 递归查询实战：CTE 递归处理树形组织架构和物料 BOM

SQL 递归查询实战：CTE 递归处理树形组织架构和物料 BOM

写过 SQL 的同学都知道，处理层级数据是个老大难问题——组织架构树、物料 BOM（Bill of Materials）、评论回复链，这些数据天然是树形或网形的，普通 JOIN 搞不定。递归 CTE（Common Table Expression）就是解决这类问题的利器。今天我们从组织架构和物料 BOM 两个实战场景出发，彻底搞懂 SQL 递归查询。

一、递归 CTE 基本原理

递归 CTE 由两部分组成：锚定成员（Anchor Member）和递归成员（Recursive Member），用UNION ALL连接。

-- 递归 CTE 的基本结构 WITH RECURSIVE cte_name AS ( -- 锚定成员：非递归的起始查询，定义递归起点 anchor_query UNION ALL -- 递归成员：引用自身，逐步展开下一层 recursive_query referencing cte_name ) SELECT * FROM cte_name;

执行过程就像剥洋葱，层层递进：

1. 锚定查询：找到根节点。
2. 递归第 1 轮：找到根节点的直接子节点。
3. 递归第 2 轮：找到第 1 层子节点的子节点。
4. 递归第 3 轮：找到第 2 层子节点的子节点。
5. 递归终止：没有新节点产生。
6. 合并结果：将所有轮次结果通过UNION ALL合并。

关键点：

• 锚定成员只执行一次，提供递归的"种子"
• 递归成员反复执行，直到没有新行产生
• 结果是所有轮次的UNION ALL合集
• MySQL 8.0+、PostgreSQL、SQLite 支持递归 CTE；SQL Server 用WITH不需要RECURSIVE关键字

为什么普通 JOIN 搞不定层级数据？假设组织架构有 5 层，用普通LEFT JOIN你要写 5 次：org o1 LEFT JOIN org o2 ON o2.manager_id = o1.emp_id LEFT JOIN org o3 ON o3.manager_id = o2.emp_id ...——如果你事先不知道最大层级数（架构随时可能调整），JOIN 次数就是未知的，没法静态写 SQL。更致命的是 BOM 场景：同一个零件 E 可能被组件 B 和组件 C 同时引用（共用件），计算 E 的总需求时，既要累加"B→E"这条路径的用量，也要累加"C→E"这条路径——普通 JOIN 展开的是一棵树而非 DAG（有向无环图），E 的重复引用会把 JOIN 结果膨胀成笛卡尔积。递归 CTE 的UNION ALL只在"父子关系"上递推，不会产生自联结的倍数膨胀，还能通过路径追踪区分"通过 B 到达 E"和"通过 C 到达 E"两条链路，分别计算各自的用量后再聚合。

二、实战场景一：组织架构树

假设我们有如下组织架构表：

-- 创建组织架构表 CREATE TABLE org_tree ( emp_id INT PRIMARY KEY COMMENT '员工ID', emp_name VARCHAR(50) COMMENT '员工姓名', dept_name VARCHAR(50) COMMENT '部门名称', manager_id INT COMMENT '直属上级ID，根节点为NULL' ); -- 插入数据：CEO 是根节点，manager_id 为 NULL INSERT INTO org_tree VALUES (1, '朱大喜', 'CEO办公室', NULL), (2, '张明', '技术部', 1), (3, '李芳', '市场部', 1), (4, '王磊', '后端组', 2), (5, '陈静', '前端组', 2), (6, '赵强', '品牌组', 3), (7, '刘洋', '数字营销组', 3), (8, '周涛', '大数据组', 4), (9, '吴燕', 'Java开发', 5), (10, '孙鹏', 'SEM专员', 7);

2.1 查询某人的全层级下属

-- 递归查询 CEO 的所有下属（含层级深度） WITH RECURSIVE subordinate_tree AS ( -- 锚定：从 CEO 开始 SELECT emp_id, emp_name, dept_name, manager_id, 1 AS level, -- 层级深度，根节点为1 CAST(emp_name AS VARCHAR(500)) AS path -- 路径字符串 FROM org_tree WHERE manager_id IS NULL -- CEO 是根节点 UNION ALL -- 递归：找到上一轮每个节点的下属 SELECT child.emp_id, child.emp_name, child.dept_name, child.manager_id, parent.level + 1, -- 层级深度递增 CONCAT(parent.path, ' -> ', child.emp_name) -- 路径拼接 FROM org_tree child INNER JOIN subordinate_tree parent ON child.manager_id = parent.emp_id -- 子节点的上级 = 上一轮的节点 ) SELECT * FROM subordinate_tree ORDER BY level, emp_id;

输出结果：

2.2 查询某人的全层级上级链

有时候我们需要反过来——从某个员工向上追溯到 CEO：

-- 递归查询某员工的所有上级链 WITH RECURSIVE manager_chain AS ( -- 锚定：从指定员工开始 SELECT emp_id, emp_name, dept_name, manager_id, 1 AS level, CAST(emp_name AS VARCHAR(500)) AS path FROM org_tree WHERE emp_id = 8 -- 查大数据组周涛的上级链 UNION ALL -- 递归：向上查找 manager SELECT mgr.emp_id, mgr.emp_name, mgr.dept_name, mgr.manager_id, child.level + 1, CONCAT(mgr.emp_name, ' -> ', child.path) FROM org_tree mgr INNER JOIN manager_chain child ON mgr.emp_id = child.manager_id -- 上级ID = 上一轮的manager_id ) SELECT * FROM manager_chain ORDER BY level DESC; -- 输出: 朱大喜 -> 张明 -> 王磊 -> 周涛（从CEO到目标员工的完整链路） 组织结构示例： - 朱大喜（CEO）直接管理张明（技术部）和李芳（市场部）。 - 张明管理王磊（后端组）和陈静（前端组）。 - 王磊管理周涛（大数据组）。 - 陈静管理吴燕（Java开发）。 - 李芳管理赵强（品牌组）和刘洋（数字营销组）。 - 刘洋管理孙鹏（SEM专员）。 ## 三、实战场景二：物料 BOM 展开 BOM 是制造业最经典的多层级数据结构——一个产品由多个组件组成，每个组件又由更小的零件组成，形成树形或网形结构。 ```sql -- 创建 BOM 表 CREATE TABLE bom ( parent_part VARCHAR(50) COMMENT '父件编号', child_part VARCHAR(50) COMMENT '子件编号', quantity DECIMAL(10,2) COMMENT '单次用量', --- level_no INT COMMENT 'BOM层级，0表示成品' ); -- 产品A 由 B、C 组成；B 由 D、E 组成；C 由 E、F 组成；E 由 G 组成 INSERT INTO bom VALUES ('A', 'B', 2.00, 1), -- A需要2个B ('A', 'C', 3.00, 1), -- A需要3个C ('B', 'D', 1.00, 2), -- B需要1个D ('B', 'E', 2.00, 2), -- B需要2个E ('C', 'E', 1.50, 2), -- C需要1.5个E ('C', 'F', 4.00, 2), -- C需要4个F ('E', 'G', 3.00, 3); -- E需要3个G

3.1 单级展开到多级展开

单级展开就是直接查某父件的所有直接子件，很简单。但多级展开需要递归，并且要累计计算总用量：

-- BOM 多级展开：计算成品A所需所有底层零件的总用量 WITH RECURSIVE bom_explode AS ( -- 锚定：从成品A开始 SELECT parent_part AS top_product, -- 顶级成品编号 child_part AS current_part, -- 当前零件编号 quantity AS unit_qty, -- 单次用量 quantity AS total_qty, -- 累计总用量（第1层就是单次用量） 1 AS bom_level, -- BOM层级 CAST(parent_part || '->' || child_part AS VARCHAR(500)) AS path FROM bom WHERE parent_part = 'A' UNION ALL -- 递归：展开下一层子件，累计用量 = 上层总用量 × 本层单次用量 SELECT parent.top_product, -- 保持顶级成品编号不变 child.child_part, child.quantity AS unit_qty, parent.total_qty * child.quantity AS total_qty, -- 累乘计算 parent.bom_level + 1, CONCAT(parent.path, '->', child.child_part) FROM bom child INNER JOIN bom_explode parent ON child.parent_part = parent.current_part -- 本层父件 = 上一层的子件 ) SELECT * FROM bom_explode ORDER BY bom_level, current_part; 关键计算逻辑：`total_qty = 上层总用量 × 本层单次用量` - A 需要 2 个 B，B 需要 2 个 E → E 的总用量 = 2 × 2 = 4 - A 需要 3 个 C，C 需要 1.5 个 E → E 的总用量 = 3 × 1.5 = 4.5 - E 的总用量合并 = 4 + 4.5 = 8.5（需要额外聚合） > **为什么 BOM 展开不能用简单的 `GROUP BY part_id` 而必须分两步？** 递归 CTE 只展开层级结构，返回的是**每条路径上的用量**，不是最终汇总。上面 E 通过两条路径出现在展开结果里：`A→B→E (total_qty=4)` 和 `A→C→E (total_qty=4.5)`——两条记录的 `current_part` 都是 E，但属于不同的物料路径。如果直接在递归里用 `GROUP BY current_part` 合并，会丢失"这个 E 是通过哪条路径来的"这个信息——而后续的质量追溯（比如 E 的质量出了问题是 B 产线还是 C 产线供的货）依赖路径信息。**铁律：递归展开负责"把层级变成扁平表 + 算每条路径的用量"，最外层 `SELECT ... GROUP BY current_part` 负责"合并同一物料的多条路径用量"——两步分离，展开管层级，聚合管汇总。** 具体到该层级结构中，成品 A 直接包含组件 B 和 C，其中 B 进一步拆解为零件 D 和 E，C 则拆解为零件 E 和 F，而零件 E 本身还包含子件 G。这种结构清晰地展示了 E 如何通过不同路径（A→B→E 与 A→C→E）被多次引用，从而印证了为何需要分离展开与聚合步骤。 ### 3.2 计算底层零件总需求 上面的递归展开了所有层级，但如果要计算最终需要多少底层零件（不再有子件的零件），还需要聚合： ```sql -- 汇总底层零件总需求 WITH RECURSIVE bom_explode AS ( -- ... 同上 ... ) SELECT ---

current_part AS bottom_part, SUM(total_qty) AS total_demand

FROM bom_explode
WHERE current_part NOT IN (
-- 排除还有子件的零件（即非底层零件）
SELECT DISTINCT parent_part FROM bom
)
GROUP BY current_part
ORDER BY total_demand DESC;

输出： | bottom_part | total_demand | |------------|-------------| | G | 25.5 (8.5×3) | | F | 12.0 (3×4) | | D | 2.0 (2×1) | ## 四、递归 CTE 的注意事项与高级技巧 ### 4.1 防止无限递归 递归 CTE 如果数据有环（比如组织架构里 A 的上级是 B，B 的上级是 A），会导致无限递归。解决方法： - MySQL 默认限制递归深度 1000（`cte_max_recursion_depth`） - PostgreSQL 没有默认限制，建议手动加 `LIMIT` - 在路径字符串中检测环：如果 `path` 已经包含当前节点，说明出现了环 ```sql -- PostgreSQL 中防环的递归查询 WITH RECURSIVE safe_tree AS ( SELECT emp_id, emp_name, manager_id, 1 AS level, ARRAY[emp_id] AS visited -- 用数组记录已访问节点 FROM org_tree WHERE manager_id IS NULL UNION ALL SELECT child.emp_id, child.emp_name, child.manager_id, parent.level + 1, parent.visited || child.emp_id -- 追加到已访问列表 FROM org_tree child INNER JOIN safe_tree parent ON child.manager_id = parent.emp_id WHERE child.emp_id != ALL(parent.visited) -- 排除已访问过的节点，防环 ) SELECT * FROM safe_tree;

4.2 递归深度控制

对于已知最大层级的数据（比如组织架构最多 10 层），可以用LIMIT提前终止：

WITH RECURSIVE tree AS ( SELECT ... FROM org_tree WHERE manager_id IS NULL UNION ALL SELECT ... FROM org_tree child INNER JOIN tree parent ON ... ) SELECT * FROM tree LIMIT 1000; -- 最多返回1000行，防止意外无限递归

4.3 递归 CTE 与普通 CTE 组合使用

递归 CTE 可以和其他普通 CTE 组合，先处理数据再递归：

WITH -- 普通CTE：先筛选活跃员工 active_org AS ( SELECT * FROM org_tree WHERE status = 'active' ), -- 递归CTE：基于活跃员工做层级展开 RECURSIVE tree AS ( SELECT * FROM active_org WHERE manager_id IS NULL UNION ALL SELECT child.* FROM active_org child INNER JOIN tree parent ON child.manager_id = parent.emp_id ) SELECT * FROM tree;

🚨 踩坑提醒

1. UNION ALL不会去重，如果你的层级数据中同一个节点有多条路径到达，展开结果会有重复行——这不是 Bug 是设计意图：BOM 场景里 E 确实应该出现两次（通过 B 和通过 C 各有自己的路径和用量）。但如果你的组织架构表里 A→B→D 和 A→C→D 都指向同一个 D，用UNION ALL展开后 D 会出现两次——这不是数据错误，是层级数据本身的多路径特性。区分场景：BOM 展开用UNION ALL（需要保留每条路径的用量信息），组织架构树如果数据约束保证单路径，改用UNION自动去重防止意外多路径。

2. MySQL 的cte_max_recursion_depth默认 1000，但数据只有 10 层也会因为"数据有环"而达到 1000 上限：一个环（A→B→C→A）只有 3 层，但递归 CTE 会在这 3 层之间无限循环，直到 hit 1000 的深度限制。MySQL 的第 1000 轮错误信息是Recursive query aborted after 1001 iterations. Try increasing ctet_max_recursion_depth——新手看到这个就会去改配置从 1000 调到 10000，完全没意识到根因是环而不是深度。排查规则：如果数据最大深度明显 < 1000 但递归仍然超限，99% 是因为有环——在路径字符串里做LOCATE(emp_id, path)检测循环，而不是无脑调大深度限制。

3. 递归 CTE 在 PostgreSQL 里不能跟在WITH子句的末尾——它必须作为最后一个 CTE，且之前可以有普通 CTE：WITH cte1 AS (...), RECURSIVE cte2 AS (...) SELECT ...是合法的，WITH RECURSIVE cte1 AS (...), cte2 AS (...) SELECT ...也是合法的。但如果写成WITH RECURSIVE cte1 AS (...), RECURSIVE cte2 AS (...)——只有第一个RECURSIVE关键字生效，第二个 CTEcte2不是递归的。这是一个纯语法陷阱，Google 都很难搜到准确解释。

递归 CTE 是 SQL 处理层级数据的利器。锚定成员定义递归起点，递归成员通过自引用逐层展开，UNION ALL合并所有层级结果。实战中，组织架构树需要记录层级深度和路径字符串，物料 BOM 需要累乘计算总用量。最关键的注意事项是防环和控深度——MySQL 有默认递归上限，PostgreSQL 需要手动限制。递归 CTE 和普通 CTE 可以组合使用，让查询逻辑更清晰。掌握递归 CTE，那些曾经让你头疼的树形数据问题，一条 SQL 就能搞定。

五、总结

本文介绍的方案在实际项目中需要经过充分验证后再全量推广。建议先在灰度环境中观察关键指标的变化，确认无异常后再逐步放量。技术在不断演进，保持学习和实践的心态，才能在架构设计上走得更远。如果在实际落地过程中遇到问题，欢迎在评论区交流讨论。