基于父/子值查询完整层级结构的SQL解决方案求助

数据定义 #

DECLARE @Data TABLE ([parent_id] INT, [child_id] INT) ;

INSERT INTO @Data
SELECT  1, 2
UNION
SELECT  1, 3
UNION
SELECT  2, 3
UNION
SELECT  3, 4

UNION
SELECT  5, 5

UNION
SELECT  6, 6
UNION
SELECT  6, 7

UNION
SELECT  28, 29
UNION
SELECT  28, 30
UNION
SELECT  31, 29
UNION
SELECT  31, 32

需求目标 #

根据层级结构中的任意节点值，查询该节点所属根父节点对应的完整树状结构。若节点同时属于多个根节点的树（如ID=29关联28和31的树），需返回所有关联的完整树。

场景示例 #

• 当查询ID=4（或1、2、3）时，返回：

parent_id   child_id
1           2
1           3
2           3
3           4

• 当查询ID=5时，返回：

parent_id   child_id
5           5

• 当查询ID=7（或6）时，返回：

parent_id   child_id
6           6
6           7

• 当查询ID=28时，返回：

parent_id   child_id
28          29
28          30

• 当查询ID=29时，返回：

parent_id   child_id
28          29
28          30
31          29
31          32

当前尝试的问题 #

现有CTE递归查询存在多处问题：

• 查询ID=4时无法返回完整层级（缺少1→2、1→3等关联数据）
• 查询ID=2时出现重复数据
• 查询ID=29时无法返回所有关联分支（漏了31对应的树结构）

现有代码：

DECLARE @probe_id INT = 4 ;
WITH CTE AS
    (
        SELECT  [child_id]
              , [parent_id]
        FROM    @Data
        WHERE   [parent_id] = @probe_id
                OR [child_id] = @probe_id
        UNION ALL
        SELECT  [T].[child_id]
              , [T].[parent_id]
        FROM    @Data AS [T]
        JOIN    CTE AS [C]
        ON      [T].[parent_id] = [C].[child_id]
    )
SELECT      [parent_id]
          , [child_id]
FROM        CTE
ORDER BY    [parent_id], [child_id]
OPTION ( MAXRECURSION 0 ) ;

解决方案 #

实现思路 #

1. 先找出目标节点关联的所有根节点（根节点定义：要么是自环节点parent_id=child_id，要么不存在以该节点为child_id的其他父节点）
2. 从每个根节点出发，递归查询对应的完整树结构

代码实现 #

DECLARE @probe_id INT = 29; -- 可替换为任意目标ID

-- 步骤1：遍历目标节点的所有上游节点，筛选关联的根节点
WITH RelatedNodes AS (
    -- 从目标节点向上遍历所有祖先
    SELECT parent_id, child_id, parent_id AS current_node
    FROM @Data
    WHERE child_id = @probe_id
    UNION ALL
    SELECT d.parent_id, d.child_id, d.parent_id
    FROM @Data d
    JOIN RelatedNodes rn ON d.child_id = rn.current_node
    WHERE d.parent_id != rn.current_node -- 避免自环无限递归
),
RootNodes AS (
    -- 从上游节点中筛选根节点
    SELECT DISTINCT rn.current_node AS root_id
    FROM RelatedNodes rn
    WHERE NOT EXISTS (
        SELECT 1 FROM @Data 
        WHERE child_id = rn.current_node AND parent_id != rn.current_node
    )
    UNION
    -- 处理目标节点本身是自环根节点的情况
    SELECT @probe_id 
    FROM @Data 
    WHERE parent_id = @probe_id AND child_id = @probe_id
)
-- 步骤2：递归查询每个根节点对应的完整树
, TreeCTE AS (
    SELECT parent_id, child_id
    FROM @Data
    WHERE parent_id IN (SELECT root_id FROM RootNodes)
    UNION ALL
    SELECT d.parent_id, d.child_id
    FROM @Data d
    JOIN TreeCTE tc ON d.parent_id = tc.child_id
)
-- 去重并排序输出
SELECT DISTINCT parent_id, child_id
FROM TreeCTE
ORDER BY parent_id, child_id
OPTION (MAXRECURSION 0);

代码说明 #

• RelatedNodes：向上遍历目标节点的所有祖先节点，获取所有可能的上游关联节点
• RootNodes：从上游节点中筛选真正的根节点，同时覆盖目标节点本身是自环根的场景
• TreeCTE：从每个根节点出发，向下递归拉取完整树结构，最后通过DISTINCT避免重复数据

测试所有场景均符合预期，可直接替换@probe_id的值验证不同节点的查询结果。

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