递归cte是sql标准中唯一跨数据库可移植的递归方案，必须包含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 （配合 <code>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层就该警惕——不是语法写不对，而是模型本身可能需要扁平化或加缓存。
