sql-server - 从根sql获取特定节点路径

Question

我想在具有树结构的表中设置数据。

DECLARE @temp TABLE 
(
      Id INT
    , Name VARCHAR(50)
    , Parent INT
)

INSERT @temp 
SELECT 1,' Great GrandFather Thomas Bishop', null UNION ALL
SELECT 2,'Grand Mom Elian Thomas Wilson' , 1 UNION ALL
SELECT 3, 'Dad James Wilson',2 UNION ALL
SELECT 4, 'Uncle Michael Wilson', 2 UNION ALL
SELECT 5, 'Aunt Nancy Manor', 2 UNION ALL
SELECT 6, 'Grand Uncle Michael Bishop', 1 UNION ALL
SELECT 7, 'Brother David James Wilson',3 UNION ALL
SELECT 8, 'Sister Michelle Clark', 3 UNION ALL
SELECT 9, 'Brother Robert James Wilson', 3 UNION ALL
SELECT 10, 'Me Steve James Wilson', 3 

如何从根节点获取特定节点的节点路径？

例如，Id IN (2, 5, 10) 的结果为:

Id   Result
 2   Great GrandFather Thomas Bishop -> Grand Mom Elian Thomas Wilson
 5   Great GrandFather Thomas Bishop -> Grand Mom Elian Thomas Wilson -> Aunt Nancy Manor
10   Great GrandFather Thomas Bishop -> Grand Mom Elian Thomas Wilson -> Dad James Wilson -> Me Steve James Wilson

对于一个 id，我使用此 T-SQL 代码，请填写它:

;WITH cte AS 
(
    SELECT *, t = 1
    FROM @temp
    WHERE Id = 10 -- <-- your id

    UNION ALL

    SELECT t2.*, t + 1
    FROM cte t
    JOIN @temp t2 ON t.Parent = t2.Id
)
SELECT STUFF((
    SELECT ' -> ' + Name
    FROM cte
    ORDER BY t DESC
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 4, '')
Go

当我使用FOR XML PATH('')时速度很低，如果没有它我如何使用您的T-SQL代码？

Answer 1

这个怎么样:

;WITH cte AS 
(
    SELECT *, t = 1, cast(name as varchar(max)) n2, id grp
    FROM @temp
    WHERE Id in (2,5,10) -- <-- your id

    UNION ALL

    SELECT t2.*, t + 1, coalesce(t2.name + ' -> ' + t.n2, t.n2), t.grp
    FROM cte t
    JOIN @temp t2 ON t.Parent = t2.Id
), cte2 as
(
SELECT grp, n2 result, row_number() over (partition by grp order by t desc) rn from cte

)
SELECT grp id, result from cte2 WHERE rn = 1

结果:

id  result
2    Great GrandFather Thomas Bishop -> Grand Mom Elian Thomas Wilson
5    Great GrandFather Thomas Bishop -> Grand Mom Elian Thomas Wilson -> Aunt Nancy Manor
10   Great GrandFather Thomas Bishop -> Grand Mom Elian Thomas Wilson -> Dad James Wilson -> Me Steve James Wilson