递归CTE构建地址层级时触发最大递归100耗尽错误求助

错误原因 #

这个错误说明你的递归CTE没有正常终止，大概率是数据中存在循环引用（比如某个节点的parent指向自身，或者形成了闭环，如A的父节点是B，B的父节点是A）；少数情况是地址层级确实超过了SQL Server默认的递归次数上限（100次）。

解决方案 #

方案1：检查并修复循环引用数据 #

先排查数据中的异常循环：

-- 1. 查找自引用的节点（ID和parent相同）
SELECT * FROM [Address] WHERE ID = Parent;

-- 2. 查找多级闭环（如A→B→C→A）
WITH cte_cycle AS (
    SELECT ID, Name, Parent, 1 AS Depth, CAST(ID AS VARCHAR(MAX)) AS NodePath
    FROM [Address]
    WHERE Parent IS NOT NULL
    UNION ALL
    SELECT a.ID, a.Name, a.Parent, c.Depth + 1, c.NodePath + '→' + CAST(a.ID AS VARCHAR(MAX))
    FROM [Address] a
    INNER JOIN cte_cycle c ON a.Parent = c.ID
    -- 排除已经在路径中的节点，避免提前终止
    WHERE a.ID NOT IN (SELECT VALUE FROM STRING_SPLIT(c.NodePath, '→'))
)
-- 筛选出存在循环的节点
SELECT * FROM cte_cycle 
WHERE ID = Parent 
OR CHARINDEX(CAST(ID AS VARCHAR), NodePath) <> CHARINDEX(CAST(ID AS VARCHAR), NodePath, CHARINDEX(CAST(ID AS VARCHAR), NodePath)+1);

找到异常数据后，修正对应的parent值（比如设置为正确的上级节点），或者删除无效的循环节点。

方案2：增加递归次数（确认无循环时使用） #

如果确认数据没有循环，只是地址层级超过了默认的100次限制，可以在查询末尾添加OPTION (MAXRECURSION N)来扩大递归上限，N可以是具体数字（如200），或者0表示无限制（谨慎使用，避免死循环）。

同时可以优化CTE，拼接出完整的地址路径，更符合你的需求：

WITH cte_address AS (
    -- 锚点：顶级节点（国家）
    SELECT 
        ID, 
        [Name], 
        Parent,
        CAST([Name] AS VARCHAR(MAX)) AS FullAddress -- 初始化完整地址
    FROM 
        [Address]
    WHERE 
        Parent IS NULL
    UNION ALL
    -- 递归：拼接子节点到地址路径
    SELECT
        a.ID, 
        a.[Name], 
        a.Parent,
        CONCAT(c.FullAddress, '→', a.[Name]) -- 拼接上级地址和当前节点名称
    FROM 
        [Address] a
    INNER JOIN 
        cte_address c ON a.Parent = c.ID
)
SELECT *  
FROM cte_address
OPTION (MAXRECURSION 200); -- 设置递归次数，根据实际层级调整

注意事项 #

• 不要随意使用OPTION (MAXRECURSION 0)，如果数据存在循环会导致无限递归，消耗大量数据库资源。
• 修复循环引用是根本解决方法，增加递归次数只是临时适配深层级场景。

内容的提问来源于stack exchange，提问作者user19446287