DB2递归CTE如何实现层次查询？

在关系数据库中保存树形结构，最常用的做法是给每一行增加一个父级引用列，例如员工表记录其上级员工编号，物料表记录其父物料号。这种存储方式结构简单、维护直观，但查询完整层级时却比较麻烦：如果不知道树有多深，就不能用固定次数的表连接来解决。DB2递归公共表表达式正是为这类需求设计的SQL能力，它通过WITH子句和UNION ALL把初始查询与递归查询合并，让数据库自己完成逐层下钻。理解递归CTE之后，组织架构、菜单树、BOM展开等需求都可以在一次SQL请求内完成。

DB2递归CTE的基本语法

递归CTE的核心思想并不复杂：先定义一个初始结果集作为递归起点，再定义一条基于当前结果集继续查找下一层数据的查询，两部分用UNION ALL连接。DB2 for LUW中递归公共表表达式直接使用WITH子句即可，不需要像某些数据库那样额外加RECURSIVE关键字。列名列表放在CTE名称后面，这样初始查询和递归查询返回的列顺序、列名能够保持一致。

下面是一个最小化的员工上下级查询示例。员工表employee包含员工编号emp_id、上级编号manager_id和员工姓名emp_name，其中最高层员工的manager_id为空。

WITH org_tree (emp_id, manager_id, emp_name, depth) AS
(
  SELECT emp_id, manager_id, emp_name, 1
  FROM employee
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.manager_id, e.emp_name, ot.depth + 1
  FROM employee e
  INNER JOIN org_tree ot ON e.manager_id = ot.emp_id
)
SELECT emp_id, emp_name, depth
FROM org_tree
ORDER BY depth, emp_id;

这个CTE由两部分构成。第一部分是从employee表中找出所有manager_id IS NULL的最高层员工，并把层级列depth初始化为1，这部分叫种子查询。第二部分把普通员工表employee与递归CTE自身org_tree进行内连接，连接条件是员工的manager_id等于递归结果中的emp_id，同时把depth加1，这部分叫递归查询。DB2先执行种子查询得到第一层数据，然后反复执行递归查询，每次用上一次递归产生的新行继续向下匹配，直到不再产生新行为止。

需要注意，递归CTE中必须包含至少一个不在递归项中出现的列用于终止条件，否则容易造成无限循环。本例中depth虽然作为输出列，但真正控制递归结束的是递归查询返回空集。实际编写时还应避免父子关系出现环形引用，例如A的员工上级是B，而B的上级又回到A，这种数据必须通过业务约束或额外过滤条件加以排除。

递归执行流程与层级控制

理解递归CTE的执行过程有助于写出正确的查询。DB2会先执行种子查询，把结果放入工作表；接着执行递归查询，但递归查询中引用的CTE名称并不是完整结果集，而是上一步刚生成的那一批行。每一轮递归只基于上一轮结果继续关联，因此即使表数据量很大，递归部分也不会每一轮都扫描所有历史行。生成的新行会追加到结果集中，并作为下一轮递归的输入。当递归查询某一次返回零行时，整个过程结束。

如果树很深，可以通过层级列限制递归深度。典型做法是在CTE中维护一个depth列，在递归查询中写WHERE ot.depth < 5，这表示只向下展开到第5层。修改后的代码如下，递归部分用层级条件提前截断，避免继续扫描过深的数据。

WITH org_tree (emp_id, manager_id, emp_name, depth) AS
(
  SELECT emp_id, manager_id, emp_name, 1
  FROM employee
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.manager_id, e.emp_name, ot.depth + 1
  FROM employee e
  INNER JOIN org_tree ot ON e.manager_id = ot.emp_id
  WHERE ot.depth < 5
)
SELECT emp_id, emp_name, depth
FROM org_tree
ORDER BY depth, emp_id;

这种写法在展示前几层组织架构或限制BOM展开成本时非常实用。不过要分清限制条件放在递归项内部和外部查询里的区别：放在递归项内部会阻止继续生成更深层数据，减少递归迭代次数；放在最终SELECT的WHERE中则只是过滤输出，数据库仍然会递归到更深层。对树很深、叶子节点很多的情况，优先在递归项内部限制层级。

递归CTE的递归项中也有不少语法限制。DB2不允许在递归项中使用DISTINCT、GROUP BY、ORDER BY、聚合函数以及针对递归CTE自身的标量子查询。这主要是为了保证递归过程可以被数据库高效处理。需要去重时可以把结果写到最终查询中用DISTINCT处理，但不要在递归部分使用UNION替代UNION ALL，因为递归语义要求保留重复行以正确形成每一轮的工作集。

典型场景：组织架构与物料BOM

组织架构查询是递归CTE最典型的应用之一。假设employee表中有员工和上级关系，目标是返回某个员工向下所有直接和间接下属。这种情况下种子查询不再固定找最高层员工，而是以指定员工为起点，递归方向仍然是从上级到下级。

WITH sub_tree (emp_id, manager_id, emp_name, depth) AS
(
  SELECT emp_id, manager_id, emp_name, 1
  FROM employee
  WHERE emp_name = '张伟'
  UNION ALL
  SELECT e.emp_id, e.manager_id, e.emp_name, st.depth + 1
  FROM employee e
  INNER JOIN sub_tree st ON e.manager_id = st.emp_id
)
SELECT emp_id, emp_name, depth
FROM sub_tree
ORDER BY depth, emp_id;

这段SQL先定位到姓名为张伟的员工，把他作为第1层，然后每次用当前层员工的emp_id去匹配下级的manager_id，就能返回张伟的所有直接下属、间接下属以及对应层级。实际使用时可以把员工姓名换成主键或工号参数，避免同名带来问题。若还想把每一条记录的完整上级路径展示出来，可以在CTE中维护一个路径列，例如拼上emp_id或姓名，递归时用当前路径加分隔符和下级编号生成新路径。

物料BOM展开是另一个常见场景。物料清单表通常包含父件编号part_id、子件编号child_part_id以及用量qty。已知一个顶层父件，需要向下展开所有子件和子子件，递归CTE可以很自然地完成这件事。

WITH bom_tree (part_id, child_part_id, qty, depth) AS
(
  SELECT part_id, child_part_id, qty, 1
  FROM bill_of_material
  WHERE part_id = 'FINISHED_GOODS_01'
  UNION ALL
  SELECT b.part_id, b.child_part_id, b.qty, bt.depth + 1
  FROM bill_of_material b
  INNER JOIN bom_tree bt ON b.part_id = bt.child_part_id
)
SELECT part_id, child_part_id, qty, depth
FROM bom_tree
ORDER BY depth, child_part_id;

BOM展开与组织架构查询的区别主要在于连接方向。上面的代码是从父件找子件，称为正展开；如果需求是找出某个子件被哪些上层组件使用，就要反向连接，把种子行设置为目标子件，然后在递归部分用当前层的父件去匹配物料清单中的子件。递归CTE的灵活性就体现在连接条件可以按业务方向调整，而整体结构仍然是种子查询加UNION ALL递归查询。

递归CTE的优化与常见问题

递归查询虽然减少应用层代码，但数据库仍要多次执行连接操作，索引设计直接影响性能。对于组织架构场景，应在上级列manager_id上建立索引，使递归部分的e.manager_id = ot.emp_id能够走索引访问而不是全表扫描。BOM展开则应在part_id或子件列上建索引。可以通过查看执行计划确认递归连接是否使用了合适的索引，如果出现全表扫描，说明每次递归都很昂贵，需要调整索引或改写连接条件。

限制递归深度是防止无限循环的重要手段。除了在递归项中加WHERE depth < N之外，也可以在表设计层面增加外键约束和业务校验，确保不会出现环状父子关系。对于已经存在脏数据的情况，可以在CTE中维护一个路径字符串，递归时判断新节点是否已经出现在路径中，以阻止环继续扩展。这种方式代价较高，但在数据量不大、关系复杂的场景下能有效避免死循环。

另外需要留意不同DB2平台对递归CTE的细微差异。DB2 for LUW使用WITH子句即可，而部分兼容SQL标准的DB2环境可能支持WITH RECURSIVE写法。编写跨平台SQL时应先确认目标数据库的文档。递归CTE还不适合处理特别深的层级，例如超过几百层的完整树，递归迭代次数过多会消耗较多临时空间，此时可以结合应用缓存或物化路径等方式优化。总体来说，DB2递归CTE为传统的父子表查询提供了一种清晰、纯粹的SQL方案，在组织、BOM、分类树等常见层次查询中非常值得优先考虑。

DB2递归CTESQL层次查询UNION ALL修改时间：2026-08-23 14:30:07