SQL Server 如何实现多级父子关系层级输出查询

你遇到的递归超限报错核心有两个问题：

1. 关联逻辑错误触发无限循环：原递归部分的关联条件CTE.[ValueID] = B.[ValueID]是将父节点和自身同ID行关联，永远无法终止递归，触发了SQL Server默认100次的递归上限。
2. 字段取值错误：递归部分取的是CTE（父节点）的字段值，就算解决循环问题，也无法输出子节点的对应信息。

如果仅需要输出每个节点的自身信息+所属层级，可直接使用简化版代码：

WITH CTE AS 
(
    -- 锚点：取最顶层父节点
    SELECT A.[Value], A.[ValueID], A.ParentValueID, 1 AS [Level]
    FROM [dbo].[MyTable] AS A
    WHERE A.ParentValueID = 0

    UNION ALL

    -- 递归：关联子节点，正确关联逻辑为父节点ID=子节点的ParentValueID，取值取子节点字段
    SELECT B.[Value], B.[ValueID], B.ParentValueID, CTE.Level + 1 
    FROM CTE 
    INNER JOIN [dbo].[MyTable] AS B ON CTE.[ValueID] = B.ParentValueID
)
SELECT * FROM CTE
-- 如果数据实际层级超过100，可取消下方注释解除递归上限限制，0代表不限制递归次数
-- OPTION (MAXRECURSION 0)

如果需要同时保留根节点信息、层级路径方便溯源，可使用扩展版代码：

WITH CTE AS 
(
    SELECT 
        A.[Value] AS RootValue,
        A.[ValueID] AS RootValueID,
        A.[Value] AS CurrentValue,
        A.[ValueID] AS CurrentValueID,
        A.[ParentValueID],
        CAST(A.[Value] AS NVARCHAR(MAX)) AS LevelPath, -- 拼接层级路径
        1 AS [Level]
    FROM [dbo].[MyTable] AS A
    WHERE A.ParentValueID = 0

    UNION ALL

    SELECT 
        CTE.RootValue,
        CTE.RootValueID,
        B.[Value] AS CurrentValue,
        B.[ValueID] AS CurrentValueID,
        B.[ParentValueID],
        CTE.LevelPath + '->' + B.[Value] AS LevelPath,
        CTE.Level + 1 AS [Level]
    FROM CTE 
    INNER JOIN [dbo].[MyTable] AS B ON CTE.CurrentValueID = B.ParentValueID
)
SELECT * FROM CTE
-- OPTION (MAXRECURSION 0)

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