sql server中cte递归查树形结构需显式指定option (maxrecursion n)，默认100层为硬限制；锚点必须在union all左侧，递归成员在右侧；parentid字段须建索引以避免性能骤降。
SQL Server里用CTE查树形结构，必须加OPTION (MAXRECURSION n)
不加这个提示，递归超过100层就直接报错 Msg 530, Level 16, State 1, Line X: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.。默认100层是硬限制，不是性能建议——它会中断整个查询，不是警告。
实际业务中，组织架构、分类目录、BOM清单经常超100级（比如多级代理、嵌套配件）。解决办法很简单：
- 查前加
OPTION (MAXRECURSION 0)表示不限制（慎用，得确保有终止条件） - 或设为合理上限，比如
OPTION (MAXRECURSION 500)，既防死循环又够用 - 注意：该选项必须放在语句末尾，且只能作用于当前
SELECT，不能写在 CTE 定义里
CTE递归必须严格区分锚点和递归成员，顺序不能反
SQL Server 要求锚点（anchor）必须在 UNION ALL 左侧，递归引用（recursive reference）必须在右侧。写反了会报 Msg 319, Level 15, State 1 错误，提示“WITH 语句中的递归部分必须是第二个或后续的 SELECT 子句”。
典型结构长这样：
WITH TreeCTE AS ( -- 锚点：查顶层节点（例如 ParentID IS NULL） SELECT ID, Name, ParentID, 0 AS Level FROM Categories WHERE ParentID IS NULL <p>UNION ALL</p><p>-- 递归成员：关联自身，向下找子节点 SELECT c.ID, c.Name, c.ParentID, t.Level + 1 FROM Categories c INNER JOIN TreeCTE t ON c.ParentID = t.ID ) SELECT * FROM TreeCTE ORDER BY Level, ID;</p>
常见错法：
- 把
INNER JOIN TreeCTE t写成LEFT JOIN—— 会导致空匹配，递归发散 - 锚点漏掉
Level或其他用于控制深度的字段，后续难过滤或排序 - 递归 JOIN 条件写成
c.ID = t.ParentID（方向反了），结果为空或乱序
查指定节点的完整路径（从根到叶子），得用字符串拼接或路径数组
单纯查子树容易，但要还原“根 → 中间 → 当前”这条链路，CTE 本身不存路径，得手动累积。最常用的是用 VARCHAR 拼接 / 分隔的路径：
WITH TreeCTE AS ( SELECT ID, Name, ParentID, CAST(Name AS VARCHAR(500)) AS Path FROM Categories WHERE ParentID IS NULL <p>UNION ALL</p><p>SELECT c.ID, c.Name, c.ParentID, CAST(t.Path + ' > ' + c.Name AS VARCHAR(500)) FROM Categories c INNER JOIN TreeCTE t ON c.ParentID = t.ID ) SELECT * FROM TreeCTE WHERE ID = 123;</p>
注意点：
-
CAST或CONVERT必须显式指定长度，否则递归中可能被截断为默认 30 字符 - 路径字段类型要在锚点就定好，不能靠隐式转换，否则递归时报类型不匹配
- 如果要按路径排序（比如实现缩进展示），可用
REPLICATE(' ', Level)生成前缀空格
性能差？多半是没给递归字段建索引
CTE 递归本质是多次 Nested Loop Join，每次都在 ParentID 上查找子节点。如果 ParentID 没索引，数据量稍大（比如上万行），查询会从秒级变成分钟级。
检查并补索引只需一句：
CREATE NONCLUSTERED INDEX IX_Categories_ParentID ON Categories(ParentID);
额外提醒：
- 别只建
ID主键索引就以为够了——递归走的是ParentID → ID查找路径 - 若表常更新，考虑加上
INCLUDE字段（如INCLUDE (Name)）减少 Key Lookup - 用
SET STATISTICS IO ON看逻辑读，递归层数越多，读取次数 ≈ 层数 × 平均子节点数
递归层级深、路径拼接长、索引缺失——这三个地方出问题，基本覆盖了 SQL Server 树查询 90% 的卡顿和报错。尤其 MAXRECURSION 和索引，最容易在测试环境没问题、上线后突然崩。
