SQL 递归 CTE 的典型应用场景

组织架构树形查询不必须用递归CTE，但绝大多数场景下它是唯一合理选择；替代方案如多层自连接仅支持固定层级且难维护，而递归CTE需注意锚点与递归部分字段一致、防止无限递归、路径拼接及排序优化等细节。

组织架构树形查询必须用递归 CTE 吗？

不是必须，但绝大多数场景下它是唯一合理选择。当需要从某个部门或员工出发，向上查所有上级（如直属领导、总监、CEO），或向下查所有下属（含隔级），WITH RECURSIVE 是标准解法。替代方案如自连接 N 次（JOIN 多次）只能处理固定层级，且语义混乱、难以维护。

常见错误是漏写 UNION ALL 中的递归部分，或把锚点（anchor）和递归（recursive）的字段顺序/类型写错，导致报错 recursive reference must be in the rightmost term of a UNION ALL。

• 锚点查询必须在 UNION ALL 左侧，递归查询在右侧
• 两部分的列数、名称、数据类型必须严格一致（可用 CAST 或别名对齐）
• 递归查询中必须引用 CTE 自身，且只能出现在 FROM 或 JOIN 子句中，不能在 WHERE 里直接用 CTE 名做过滤

如何防止无限递归导致查询卡死？

数据库不会自动终止无终止条件的递归，尤其当存在循环引用（如 A→B→A）时，WITH RECURSIVE 会持续执行直到超时或内存耗尽。PostgreSQL 和 SQL Server 支持 MAXRECURSION 或 search_depth 限制，但 MySQL 8.0+ 仅靠 cte_max_recursion_depth 系统变量全局控制，无法按查询设置。

更可靠的做法是在递归逻辑中显式拦截：

• 用 ARRAY[emp_id]（PG）或字符串拼接路径（如 '/1/5/22/'）记录已访问节点，每次递归前检查当前 emp_id 是否已在路径中
• 加 depth 计数器，初始为 1，每次 +1，并在 WHERE depth 类条件中截断
• 避免在锚点里选多个根节点后直接递归——这会触发笛卡尔式展开，应先用 UNION ALL 分别启动各分支，或在外层用 IN 匹配

路径聚合（如 “CEO > 总监 > 经理 > 员工”）怎么写？

递归 CTE 本身不提供路径拼接函数，需手动累积。不同数据库语法差异明显：PostgreSQL 可用 array_to_string(ARRAY['CEO', '总监', ...], ' > ') 配合 array_prepend()；SQL Server 用 CONCAT() 或 + 字符串拼接；MySQL 8.0+ 推荐 CONCAT_WS(' > ', ...)，但注意空值会导致整段变 NULL，需套 COALESCE()。

典型结构是：锚点中初始化路径字段（如 name AS path），递归部分用 CONCAT(parent.p

ath, ' > ', child.name)（SQL Server）或 parent.path || ' > ' || child.name（PG）更新。

• 路径字段建议定义为 VARCHAR 并预留足够长度（如 2000），避免截断
• 若需按路径排序（如树形展示），可同时维护一个 sort_path 字段，用定长数字补零拼接（如 '0001.0005.0022'），比字符串排序更准确
• 不要在路径字段上建索引——它几乎不参与 WHERE 过滤，纯属输出用途

递归 CTE 能替代程序端遍历吗？

能，而且通常应该替代。比如前端请求「用户所属全部部门及上级部门」，过去常由应用代码循环查 parent_id，发 5～10 次 SQL；现在一条递归 CTE 就能返回完整结果集，网络和解析开销大幅下降。

但要注意边界：如果递归深度超过几百层，或结果集达数万行，数据库内存压力会陡增，此时反而是分页+客户端缓存更稳。另外，递归 CTE 无法做复杂的业务判断（如“跳过状态为离职的节点”需在递归 WHERE 中过滤，但无法调用存储过程或复杂 UDF）。

• 简单层级关系（组织、分类、BOM 物料清单）优先用递归 CTE
• 涉及实时权限校验、多源数据拼接、或需中间状态计算的，别硬塞进 CTE
• MySQL 用户要确认版本 ≥ 8.0.1，且 cte_max_recursion_depth 设置得当，否则默认只允许 100 层

真正难的不是写出第一个递归 CTE，而是想清楚「终止条件是否覆盖所有环路」「路径字段会不会溢出」「要不要提前剪枝」——这些细节往往在线上跑了一周后才暴露。