关系型数据库擅长处理二维表格数据，但当业务模型本身是一个树或者图——比如组织架构、地区层级、物料清单（BOM）时，表与表之间的行不再是平等并列，而是呈现出父子链接。在这种场景下，想获取某个节点的所有子孙或者从叶子一路回溯到根，传统的自连接或多次查询就暴露出弱点：层数固定且不可扩展。SQL标准为此引入了递归公共表表达式（Recursive Common Table Expression），也就是WITH RECURSIVE，它让数据库引擎在一条语句内完成迭代式的数据遍历。
递归CTE的语法结构与执行原理
从语法上看，WITH RECURSIVE出现在SELECT语句之前，由关键字WITH RECURSIVE、CTE名称、列名列表以及一个UNION ALL集合组成。这个UNION ALL连接了两部分：一部分叫锚成员（anchor member），另一部分叫递归成员（recursive member）。锚成员是递归的起点，通常是一次性的非递归查询，用来找出初始行集。递归成员则引用CTE自身的名称，每次执行时以锚成员或上一轮迭代的结果作为输入，产出新的行，并再次被CTE自身引用。数据库引擎会反复执行递归成员，直到它返回空集为止，整个过程自动完成。
以一个生成连续数字序列的例子来说明：我们想让数据库生成1到10的数字。锚成员可以写成SELECT 1 AS n，递归成员则写成SELECT n+1 FROM cte WHERE n < 10。执行时，引擎先计算锚成员得到1，然后将这个1送入递归成员得到2，接着把2作为下一轮的输入产生3，如此重复，当n等于10时递归成员会多产出一行11，但由于WHERE条件n<10会立即终止，最终结果集就是1到10。这里的关键点在于，递归成员中引用的CTE名称代表的是上一轮递归产出的全部行，而不是整个结果集。
需要特别注意，默认递归深度在不同数据库有不同限制。例如PostgreSQL默认允许递归迭代最多100次，MySQL 8.0通过cte_max_recursion_depth系统变量来限制。这有助于防止意外写出的无限循环吞光服务器资源。
WITH RECURSIVE numbers(n) AS (
SELECT 1 -- 锚成员
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 10 -- 递归成员
)
SELECT * FROM numbers;
利用递归查询处理树形结构
最常见的递归查询场景莫过于树形数据的管理。假设有一张部门表departments，包含id、name和parent_id三列，parent_id指向该部门上级的id，根部门的parent_id为NULL。如果要找出某个部门及其所有下级部门，就可以用递归CTE轻松实现。锚成员直接查找目标部门本身，递归成员则从CTE当前的结果行出发，连接departments表找出parent_id为当前id的记录，这样一层层向下延伸，最终得到完整的子树。
与向下遍历对称的还有向上回溯。比如从某个员工所在的部门出发，逐级向上查到根部门的完整路径。此时锚成员定位到该员工对应的部门记录，递归成员则以cte.parent_id = d.id的方式连接departments表，一直往上查到parent_id为NULL时结束。这在实际开发中经常会用于面包屑导航的生成，或分析某一节点在整个层级中的定位。
针对复杂需求，还可以在递归过程中携带额外的信息。例如除了部门id，同时记录从根节点到当前节点的路径字符串，这对展示缩进层次或生成物料编码非常有帮助。示例中，路径可以通过拼接操作符逐步构建：锚成员时为部门名称，递归成员时将父级路径与当前名称用分隔符连起来，这样最终每行都包含了完整的层级路径。
WITH RECURSIVE dept_tree AS (
SELECT id, name, parent_id, name AS path
FROM departments
WHERE parent_id IS NULL
UNION ALL
SELECT d.id, d.name, d.parent_id, dt.path || ' > ' || d.name
FROM departments d
INNER JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree;
递归查询的常见陷阱与优化策略
递归CTE虽然强大，但如果使用不当也容易引发性能问题甚至让数据库陷入死循环。首先要警惕的就是“环”的出现。在某些数据模型中，如果父子关系意外形成了闭环——比如部门A的上级是B，而B的上级又被错误设置成了A——那么递归查询将永远无法自动终止，最终因超出深度限制而报错。许多数据库提供了循环检测机制，例如PostgreSQL支持CYCLE子句，MySQL 8.0则需要在递归成员中手动维护访问路径并使用FIND_IN_SET之类的函数来排除已访问节点。
另一个性能陷阱是索引的缺失。递归成员每次迭代都要根据CTE的当前结果去关联基础表，如果没有合适的索引支持，检索每一层的效率会非常低。对于向下遍历，通常需要在parent_id列上建立索引；对于向上遍历，则应在主键及被关联的外键列上建好索引。此外，递归深度本身也会影响执行时间，如果一棵树的层级很深，比如几百层的物料BOM，迭代次数会非常大，此时可以考虑结合缓存或物化路径等其他方案来减轻数据库负担。
还有一点需要留意：递归CTE内部不能直接使用聚合函数或ORDER BY、LIMIT等限定单个集合的操作。如果需要去重或者排序，要在最终的SELECT语句中处理。同时，不同数据库的递归支持程度有所差异，SQLite需要启用特定编译选项，MySQL低于8.0的版本则完全不具备递归CTE能力，此时可能不得不借助存储过程或程序层递归。在日常设计时，如果预计层次结构会频繁查询，也可以预先计算并维护一张闭包表，以空间换取查询的简单高效。
-- PostgreSQL 使用CYCLE子句避免循环
WITH RECURSIVE dept_tree AS (
SELECT id, name, parent_id
FROM departments
WHERE parent_id IS NULL
UNION ALL
SELECT d.id, d.name, d.parent_id
FROM departments d
INNER JOIN dept_tree dt ON d.parent_id = dt.id
) CYCLE id SET is_cycle USING path
SELECT * FROM dept_tree;
SQL递归查询WITH_RECURSIVE递归CTE修改时间：2026-08-12 12:06:57