SQL存储过程复杂递归查询实现方法

刚转投 SQL Server 的朋友经常会碰到这样一个场面：兴致勃勃写完一段递归 CTE，一执行，啪——Incorrect syntax near the keyword 'RECURSIVE'。别慌，这不是你写错了逻辑，而是 SQL Server 的语法方言在作怪。它不支持 PostgreSQL 或 MySQL 那种 WITH RECURSIVE 的写法，必须老老实实用 WITH + UNION ALL 结构，而且对引用顺序和递归成员有严格要求。下面把几个最容易踩的坑捋一捋，顺便聊聊怎么写出既正确又高效的递归查询。

存储过程里写递归 CTE，第一个硬性条件是 WITH 必须是所在分支的第一条语句。很多人习惯在 IF 或 BEGIN...END 里先写点别的，比如一个 SELECT 或者变量赋值，然后再接 WITH，结果直接语法报错。正确做法是把整个 WITH 块放到分支的开头。如果有多路逻辑，比如根据不同参数走不同的递归树，建议拆成多个独立的 WITH 块，而不是试图在一个 CTE 里塞进所有条件分支——那样不仅可读性差，还容易触发各种怪异的错误。

存储过程里写递归CTE，WITH 必须是第一条语句

常见错误是把 WITH 套在 IF 或 BEGIN...END 里，但又没让它成为该分支的首条可执行语句。SQL Server 要求 WITH 必须是批处理（或分支作用域）中的第一个关键字。

• 错：在 IF @rootId IS NOT NULL BEGIN SELECT ...; WITH Tree AS (...) ... END ——WITH 前有 SELECT，语法报错
• 对：把整个 WITH 块放在 IF 分支开头，例如：IF @rootId IS NOT NULL BEGIN WITH Tree AS (...) SELECT * FROM Tree OPTION (MAXRECURSION 500); END
• 如果需要多路逻辑（如按不同条件走不同树），建议拆成多个独立的 WITH 块，而不是试图在一个 CTE 里塞所有分支逻辑

递归深度超限就卡住？别硬扛，默认100层真会炸

查组织架构或评论嵌套时，很容易触发 The maximum recursion 100 has been exhausted。这不是超时，是 SQL Server 主动终止，且不会返回部分结果。

• OPTION (MAXRECURSION n) 必须加在最终 SELECT、INSERT 等语句末尾，不能写在 CTE 定义里
• OPTION (MAXRECURSION 0) 表示不限制，但生产环境禁用——循环引用或数据异常会导致会话卡死、内存耗尽
• 建议把 @maxRecursion 设为存储过程输入参数，由调用方根据业务场景控制，比如“最多展开5级部门”就传 5

UNION ALL 写成 UNION？结果可能漏掉整层节点

递归 CTE 中必须用 UNION ALL，不是风格偏好，是语法硬性要求。MySQL 和 PostgreSQL 在递归部分强制校验，SQL Server 虽不报语法错，但用 UNION 会导致去重逻辑误删层级数据。

• 假设某员工有两个同名下属（name = '张工'），用 UNION 后第二层只留一个，树就断了
• 递归过程本质是“逐层追加”，不是“合并去重”，UNION ALL 才符合语义
• 检查执行计划时，若发现 Sort 算子出现在递归分支里，大概率是误用了 UNION

查出来怎么按树形缩进显示？别用 LEFT JOIN 自关联

要让结果带层级缩进（比如 level=0 显示为 马总，level=1 为 张工），最稳的方式是用 REPLICATE 拼空格，而不是靠自连接或窗口函数模拟。

• 示例：SELECT REPLICATE(' ', t.level * 4) + t.name AS display_name, t.* FROM Tree t
• 自关联方式（比如 LEFT JOIN Tree t2 ON t2.level = t.level - 1）在深层递归时性能急剧下降，且容易因 JOIN 条件漏匹配导致缩进错乱
• REPLICATE 是标量计算，无数据依赖，执行稳定，渲染层也容易解析前缀空格

真正难的不是写出递归语句，而是当树深超过 20 层、节点数过万时，如何让 manager_id 上的索引被真正用上——这时候执行计划里常出现“未使用索引”的警告，得回头检查递归成员里的 JOIN 条件是否隐式转换了类型。