递归CTE必须包含锚点和递归成员两部分,锚点提供初始结果集且须独立执行,递归成员须引用自身并仅位于UNION ALL右侧;列名、类型须对齐;必须显式设置MAXRECURSION防死循环;WHERE不能下推至递归分支内,过滤只能在锚点或最终SELECT外层进行。

递归CTE必须包含锚点和递归成员两部分
缺一不可,否则SQL Server会报错 Recursive common table expression ' 或类似提示。锚点(anchor)是初始结果集,递归成员(recursive member)必须引用自身,且只能出现在 UNION ALL 的右侧(不能用 UNION,不支持去重)。
常见错误是把条件写反:比如在锚点里过滤了子节点,在递归里又试图查父节点——逻辑断裂导致返回空或死循环。
- 锚点查询必须能独立执行,不依赖CTE名
- 递归成员中对CTE的引用必须是直接的,不能嵌套在子查询或视图里
- 所有列名和数据类型必须在锚点中定义好,递归成员要严格对齐(列数、顺序、类型隐式转换要安全)
必须显式设置 MAXRECURSION 选项防死循环
SQL Server默认最多递归100层,超过就报错 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.。这不是“不够用”才加,而是只要可能深度不确定,就必须设——尤其在存储过程中处理组织架构、BOM清单、评论树这类数据时。
在存储过程里,不能靠客户端连接设置 SET MAXRECURSION,必须在 SELECT 或 INSERT/UPDATE 语句末尾加 OPTION (MAXRECURSION 。
-
OPTION (MAXRECURSION 0)表示不限制(慎用,务必确保有终止条件) -
OPTION (MAXRECURSION 32767)是最大允许值,适合已知深度但较深的场景 - 如果业务上树深绝不会超50,设成
55比设0更安全——既能捕获异常数据,又不会误杀正常请求
WHERE 条件不能下推到递归成员内部
很多人想在递归部分加 WHERE parent_id = @target 来“加速”,结果发现只返回一层就停了。因为递归CTE的执行模型是:先跑锚点 → 把结果喂给递归成员 → 生成下一层 → 再喂入……整个过程不支持在递归分支里动态剪枝。
真正有效的过滤只发生在两个地方:锚点里限定起点,最终 SELECT 外层再筛结果。中间层必须无条件展开,否则会漏数据。
- 错误写法:
SELECT * FROM tree WHERE id = @start UNION ALL SELECT t.* FROM tree t INNER JOIN cte ON t.parent_id = cte.id WHERE t.status = 'active'——WHERE在递归分支内,被忽略 - 正确写法:锚点确保
id = @start,递归部分无条件关联,最终SELECT ... FROM cte WHERE status = 'active' - 若需按路径过滤(如“只取第3级以下”),用
LEVEL列(手动计数)配合外层WHERE
在存储过程中引用递归CTE要避免变量作用域陷阱
CTE只是语法结构,不是对象,不能跨语句使用。你在存储过程里写了 WITH cte AS (...),它只对紧跟着的那一条 SELECT/INSERT/UPDATE 生效。如果后面还要查一次,必须重写整个CTE,或者把结果存进临时表。
更隐蔽的问题是参数传递:CTE本身不接受参数,所有过滤条件必须通过锚点查询带入。如果你在存储过程中用 @dept_id,它只能出现在锚点的 WHERE 里,不能在递归部分“重新读取”该变量值(因为递归部分运行时变量值没变,但逻辑上你可能误以为它会动态重算)。
- 需要多次使用结果?先插进
#temp或@table_var,再反复查 - 要根据不同起点跑多次递归?用循环或游标调用同一段CTE逻辑(每次传新参数进锚点)
- 别在CTE里写
GETDATE()或NEWID()——它们会在每层递归重复求值,不是锚点算一次就固定
递归CTE真正的复杂点不在语法,而在对“执行顺序”和“数据流边界”的理解。很多人调试时盯着递归部分改来改去,其实问题出在锚点没兜住起点,或者外层没拦住无效分支。











