SQL Server中使用CTE递归查询父级树失败求助

问题场景 #

你在SQL Server中首次尝试用递归CTE获取父级树，但查询仅返回根节点，无法得到所有子节点，同时希望生成类似QQQ -> MMM -> AAA -> GGG的自上而下路径字符串。

测试表结构 #

CREATE TABLE recursiveQuery
(
    id numeric(13),
    name varchar(50),
    parent numeric(13),
    parentName varchar(50)
)

测试数据 #

insert into recursiveQuery values(1, 'QQQ', NULL, NULL)
insert into recursiveQuery values(44, 'MMM', 1, 'QQQ') 
insert into recursiveQuery values(33, 'AAA', 44, 'MMM')
insert into recursiveQuery values(22, 'GGG', 33, 'AAA')
insert into recursiveQuery values(55, 'JJJ', 33, 'AAA')
insert into recursiveQuery values(66, 'PPP', 1, 'QQQ') 

你的错误查询 #

WITH parent_tree AS 
(
    SELECT
        id, name, parent, parentName, 1 AS level
    FROM 
        recursiveQuery m
    WHERE 
        parent IS NULL

    UNION ALL
 
    SELECT 
        rr.id, rr.name, rr.parent, rr.parentName, level + 1 AS level
    FROM 
        recursiveQuery rr
    INNER JOIN 
        parent_tree r ON r.parent = rr.id 
)
SELECT *
FROM parent_tree 
ORDER BY level
OPTION(MAXRECURSION 0)

错误原因 #

递归部分的JOIN条件逻辑完全写反了：你用r.parent = rr.id，意思是找“父级CTE结果的parent等于当前表的id”，这根本匹配不到任何子节点。正确的关联应该是子节点的parent等于父级CTE结果的id，也就是rr.parent = r.id。

修正后的查询（返回全量节点） #

WITH parent_tree AS 
(
    SELECT
        id, name, parent, parentName, 1 AS level
    FROM 
        recursiveQuery m
    WHERE 
        parent IS NULL

    UNION ALL
 
    SELECT 
        rr.id, rr.name, rr.parent, rr.parentName, r.level + 1 AS level
    FROM 
        recursiveQuery rr
    INNER JOIN 
        parent_tree r ON rr.parent = r.id -- 修正关联条件
)
SELECT *
FROM parent_tree 
ORDER BY level, id
OPTION(MAXRECURSION 0)

生成自上而下的路径字符串 #

如果要生成类似QQQ -> MMM -> AAA -> GGG的路径，只需在CTE中新增路径字段，递归时拼接父路径和当前节点名称即可：

WITH parent_tree AS 
(
    SELECT
        id, name, parent, parentName, 
        1 AS level,
        CAST(name AS VARCHAR(MAX)) AS path -- 初始化根节点路径
    FROM 
        recursiveQuery m
    WHERE 
        parent IS NULL

    UNION ALL
 
    SELECT 
        rr.id, rr.name, rr.parent, rr.parentName, 
        r.level + 1 AS level,
        CAST(r.path + ' -> ' + rr.name AS VARCHAR(MAX)) AS path -- 拼接路径
    FROM 
        recursiveQuery rr
    INNER JOIN 
        parent_tree r ON rr.parent = r.id
)
SELECT id, name, parent, parentName, level, path
FROM parent_tree 
ORDER BY level, id
OPTION(MAXRECURSION 0)

执行后会得到包含完整路径的结果，比如GGG对应的路径是QQQ -> MMM -> AAA -> GGG，PPP对应的路径是QQQ -> PPP。

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