SQL递归子查询如何改写为递归CTE

在SQL标准中，递归CTE是唯一可在不同数据库间移植的递归方案。它必须包含WITH RECURSIVE关键字、锚点查询（比如WHERE parent_id IS NULL）以及递归成员（通过UNION ALL连接并引用自身CTE），这三者缺一不可。而普通子查询不能自引用，只能进行单层查询。

为什么不能直接用子查询实现递归

SQL标准是不支持在普通子查询里引用自身的，像SELECT * FROM t WHERE id IN (SELECT parent_id FROM t)这种写法，它只能查询一层，没办法自动展开树形结构。你所看到的“递归子查询”，很多时候其实是应用层拼SQL，或者是数据库方言（比如Oracle CONNECT BY）的特有语法，并非通用的SQL能力。

真正跨数据库可移植的递归方案只有递归CTE（Common Table Expression），它通过 WITH RECURSIVE 显式定义锚点（base case）和递归成员（recursive member）两部分。

递归CTE必须包含的三个要素

缺一不可，漏掉任意一个都会报错或返回空结果：

• WITH RECURSIVE 关键字（PostgreSQL/SQLite/SQL Server 支持；MySQL 8.0+ 也支持，但需显式声明）
• 锚点查询（非递归初始结果集，比如根节点 WHERE parent_id IS NULL）
• 递归查询（用 UNION ALL 连接，且必须从CTE自身引用，如 JOIN tree ON t.parent_id = tree.id）

示例：查某个部门下所有子部门（含自身）

WITH RECURSIVE dept_tree AS (
-- 锚点：起始部门
SELECT id, name, parent_id FROM departments WHERE id = 123
UNION ALL
-- 递归：找子部门
SELECT d.id, d.name, d.parent_id
FROM departments d
INNER JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree;

容易踩的坑：循环引用与性能失控

递归CTE不会自动检测环路，如果数据存在自引用或闭环（比如 A→B→C→A），查询会无限循环直到超时或达到最大递归深度限制。

不同数据库默认限制不同：max_recursive_iterations（MySQL）、maxrecursion（SQL Server）、search_path 或手动加 LEVEL 计数器（PostgreSQL）——务必主动设防：

• 加 WHERE level （配合 SELECT ..., 1 AS level 和 dt.level + 1）
• 用 ARRAY[cte.id] AS path + NOT id = ANY(path) 检测重复ID（PostgreSQL）
• 避免 UNION（去重开销大），一律用 UNION ALL；去重留到外层处理

Oracle/旧版MySQL用户怎么过渡

Oracle用户习惯 CONNECT BY PRIOR，改写时注意对应关系：START WITH → 锚点查询，CONNECT BY → 递归JOIN条件，LEVEL → 手动计数字段。

MySQL 5.7及更早版本不支持递归CTE，只能靠应用层迭代或临时表模拟；升级到8.0+后，确认开启配置：SET SESSION cte_max_recursion_depth = 1000，否则默认只允许100层。

真实业务中，树深度超过5层就该警惕——不是语法写不对，而是模型本身可能需要扁平化或加缓存。