SQL 递归查询实战CTE 递归处理树形组织架构和物料 BOM写过 SQL 的同学都知道处理层级数据是个老大难问题——组织架构树、物料 BOMBill of Materials、评论回复链这些数据天然是树形或网形的普通 JOIN 搞不定。递归 CTECommon 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 层子节点的子节点。递归终止没有新节点产生。合并结果将所有轮次结果通过UNION ALL合并。关键点锚定成员只执行一次提供递归的种子递归成员反复执行直到没有新行产生结果是所有轮次的UNION ALL合集MySQL 8.0、PostgreSQL、SQLite 支持递归 CTESQL 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;输出结果emp_idemp_namedept_namelevelpath1朱大喜CEO办公室1朱大喜2张明技术部2朱大喜 - 张明3李芳市场部2朱大喜 - 李芳4王磊后端组3朱大喜 - 张明 - 王磊...............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个G3.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 个 BB 需要 2 个 E → E 的总用量 2 × 2 4 - A 需要 3 个 CC 需要 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_qty4) 和 A→C→E (total_qty4.5)——两条记录的 current_part 都是 E但属于不同的物料路径。如果直接在递归里用 GROUP BY current_part 合并会丢失这个 E 是通过哪条路径来的这个信息——而后续的质量追溯比如 E 的质量出了问题是 B 产线还是 C 产线供的货依赖路径信息。**铁律递归展开负责把层级变成扁平表 算每条路径的用量最外层 SELECT ... GROUP BY current_part 负责合并同一物料的多条路径用量——两步分离展开管层级聚合管汇总。** 具体到该层级结构中成品 A 直接包含组件 B 和 C其中 B 进一步拆解为零件 D 和 EC 则拆解为零件 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_demandFROM bom_explodeWHERE current_part NOT IN (-- 排除还有子件的零件即非底层零件SELECT DISTINCT parent_part FROM bom)GROUP BY current_partORDER 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 的上级是 BB 的上级是 A会导致无限递归。解决方法 - MySQL 默认限制递归深度 1000cte_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; 踩坑提醒UNION ALL不会去重如果你的层级数据中同一个节点有多条路径到达展开结果会有重复行——这不是 Bug 是设计意图BOM 场景里 E 确实应该出现两次通过 B 和通过 C 各有自己的路径和用量。但如果你的组织架构表里 A→B→D 和 A→C→D 都指向同一个 D用UNION ALL展开后 D 会出现两次——这不是数据错误是层级数据本身的多路径特性。区分场景BOM 展开用UNION ALL需要保留每条路径的用量信息组织架构树如果数据约束保证单路径改用UNION自动去重防止意外多路径。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)检测循环而不是无脑调大深度限制。递归 CTE 在 PostgreSQL 里不能跟在WITH子句的末尾——它必须作为最后一个 CTE且之前可以有普通 CTEWITH 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 就能搞定。五、总结本文介绍的方案在实际项目中需要经过充分验证后再全量推广。建议先在灰度环境中观察关键指标的变化确认无异常后再逐步放量。技术在不断演进保持学习和实践的心态才能在架构设计上走得更远。如果在实际落地过程中遇到问题欢迎在评论区交流讨论。