递归CTE必须用UNION ALL而非UNION，因需保留重复行并维持层级结构；锚点与递归部分须正确关联、限制深度、统一路径格式，并避免笛卡尔积。
递归CTE必须用 UNION ALL，不能用 UNION
SQL Server、PostgreSQL、SQLite 等支持递归 CTE 的数据库中，WITH RECURSIVE（或 SQL Server 的 WITH）的锚点和递归部分之间必须用 UNION ALL 连接。用 UNION 会导致报错或逻辑错误——因为递归需要保留重复行（比如同一节点被多条路径访问），且 UNION 的去重会破坏递归层级结构。
常见错误现象：Recursive common table expression 'tree' does not contain a recursive reference（SQL Server）或 PostgreSQL 报 recursive reference must be in rightmost term of UNION，往往就是用了 UNION 或把递归查询写在了 UNION 左边。
- 锚点查询（第 0 层）返回根节点，必须包含层级字段（如
level）和路径字段（如path） - 递归查询中，JOIN 必须关联到上一层的 CTE 别名（如
JOIN tree ON t.parent_id = tree.id），不能反着写 - 路径拼接推荐用字符串函数：SQL Server 用
CONCAT(tree.path, '/', t.name)，PostgreSQL 用tree.path || '/' || t.name，避免+导致 NULL 消失
JOIN 递归CTE时必须显式限制递归深度
递归 CTE 自身不带终止条件，一旦存在环（比如 parent_id 指向自己或形成闭环），就会无限循环直到超时或达到最大递归数（SQL Server 默认 100，PostgreSQL 默认无限制但会 OOM）。所以 JOIN 之前，必须在 CTE 内部加 level < N 条件。
使用场景：查某个部门下所有子部门及员工，但业务上最多只允许展开 5 级；或者前端树组件只支持展示 4 层。
- SQL Server：在
OPTION (MAXRECURSION N)是最后补丁，不是替代方案；真正可控的是在递归分支 WHERE 中加tree.level < 5 - PostgreSQL：必须在递归查询的
WHERE子句里写tree.level < 5，否则max_recursion_depth是全局设置，不推荐依赖 - JOIN 外层表（比如
JOIN users u ON u.dept_id = tree.id）不会自动继承递归限制，所以限制必须在 CTE 定义内完成
路径字符串比较要用 LIKE 而非 =，尤其查子树
当用递归 CTE 构建了 path 字段（如 /1/5/23），想查节点 5 下所有后代，不能写 WHERE path = '/1/5/23'，而要写 WHERE path LIKE '/1/5/%'。否则只能匹配到叶子节点，漏掉中间层级。
容易踩的坑：前端传入一个 parent_path（比如 /1/5），后端直接拼成 WHERE path = :parent_path，结果永远查不到子节点。
- 确保路径格式统一：开头加
/，结尾不加，避免/1/5和/1/50被误匹配 - PostgreSQL 可用
path ~ '^/1/5(/|$)'更精确；SQL Server 可用LEFT(path, LEN(@parent_path)) = @parent_path AND SUBSTRING(path, LEN(@parent_path)+1, 1) = '/' - 如果用 JSON 数组存路径（如
[1,5,23]），就别走字符串 LIKE，改用@path::jsonb @> [1,5]::jsonb（PostgreSQL）或JSON_CONTAINS（MySQL 8.0+）
JOIN 多次递归CTE容易触发笛卡尔积
一个常见需求是：查每个部门的负责人 + 该部门所有员工。如果分别定义两个递归 CTE（一个查部门树，一个查汇报线树），再 JOIN，很可能因层级不齐导致爆炸式连接——比如部门树有 3 层，汇报线树有 5 层，JOIN 后行数 × 倍增长。
性能影响明显：100 个部门 × 平均 20 名员工 → 预期 2000 行，但笛卡尔积可能产出 10 万+ 行，查询直接卡死。
- 优先用单个递归 CTE 构建完整上下文（如同时带
dept_id,manager_id,employee_id），再 LEFT JOIN 关联用户表 - 避免在递归分支里 JOIN 外部大表（如
JOIN orders o ON o.user_id = t.id），这会让每层递归都扫一遍订单表 - 必要时加
DISTINCT ON (t.id)（PostgreSQL）或ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY tree.level)去重
路径拼接和层级控制是递归 CTE 的核心约束点，不是语法糖——少写一个 level < 5，或路径没加前导斜杠，线上就可能跑出几百万行临时结果。
