with recursive 是主流数据库支持的标准递归查询方式，结构含非递归成员与递归成员两部分，需用 union all 连接；使用时须注意数据库差异、防环机制、索引优化及深度限制。
WITH RECURSIVE 是 SQL 实现树形结构或层级关系递归查询的唯一标准方式，主流数据库（PostgreSQL、SQL Server、SQLite 3.8.3+、MySQL 8.0+）都支持，但语法细节和限制差异很大——别直接抄示例，先看清楚你用的是哪个数据库。
递归 CTE 的基本结构长什么样
递归 CTE 必须包含两个部分：**非递归成员（anchor）** 和 **递归成员（recursive term）**，用 UNION ALL 连接（不能用 UNION，否则可能截断循环路径）。
典型结构：
WITH RECURSIVE tree AS ( -- 非递归部分：起始节点（比如所有顶级部门） SELECT id, name, parent_id, 0 AS level FROM departments WHERE parent_id IS NULL <p>UNION ALL</p><p>-- 递归部分：基于上一层结果继续 JOIN SELECT d.id, d.name, d.parent_id, t.level + 1 FROM departments d INNER JOIN tree t ON d.parent_id = t.id ) SELECT * FROM tree ORDER BY level, id;</p>
注意：tree 在递归分支中必须作为右表被引用，且 JOIN 条件要确保能收敛（比如子→父 或 父→子方向明确）。
MySQL 8.0+ 中容易踩的三个坑
MySQL 对递归 CTE 的限制比 PostgreSQL 更严格，常见报错和应对方式：
-
ERROR 3636: Recursive query aborted after 1001 iterations：默认最大递归深度是 1000，需显式设置cte_max_recursion_depth。执行前加：SET SESSION cte_max_recursion_depth = 3000; - 递归分支里不能有聚合函数、窗口函数、
GROUP BY、ORDER BY（除非在最外层）、子查询（除相关标量子查询外） - 递归引用必须是
FROM子句中的直接表名或 CTE 名，不能套多层视图或派生表；SELECT *在递归分支中不被允许，字段必须显式列出
如何防止无限循环（尤其处理脏数据时）
真实业务中常遇到环状引用（比如 A→B→C→A），WITH RECURSIVE 本身不自动检测环，得靠人工加防护：
方案一：用数组/字符串记录已遍历路径（PostgreSQL 支持 ARRAY，MySQL 可用 JSON_ARRAY 或拼接逗号字符串）：
-- PostgreSQL 示例：用 path 数组避免重复访问 WITH RECURSIVE tree AS ( SELECT id, name, parent_id, ARRAY[id] AS path FROM departments WHERE parent_id IS NULL <p>UNION ALL</p><p>SELECT d.id, d.name, d.parent_id, t.path || d.id FROM departments d INNER JOIN tree t ON d.parent_id = t.id WHERE NOT d.id = ANY(t.path) -- 防环关键 ) SELECT * FROM tree;</p>
方案二：加层数硬限制（如 level ），适合已知最大深度的场景；方案三：预检查是否存在环（用自连接或图算法工具离线跑）。
性能差？先确认是不是“递归扫描”变“全表嵌套”
递归 CTE 的执行计划不是“一次编译、多次调用”，而是按层级展开为多个 JOIN 步骤。如果递归条件没走索引，每一层都会触发全表扫描。
务必确保递归 JOIN 的字段有索引，例如：
- 父→子模式（上面例子）：
departments(parent_id)要有索引 - 子→父模式（查某节点的所有上级）：
departments(id)必须有主键或唯一索引，否则递归时无法快速定位父行 - 避免在递归分支里对 CTE 别名做
WHERE过滤（如WHERE t.level ），应放在最终 <code>SELECT外层，否则可能干扰优化器
真正难的不是写出来，是验证它在 10 万行数据下是否仍只扫几千行——拿 EXPLAIN ANALYZE（PostgreSQL）或 EXPLAIN FORMAT=TREE（MySQL 8.0.16+）看实际迭代次数和扫描行数。
