sql-server - 获取树中节点的路径

Question

我想要获取树中特定节点的路径。请查看我的树数据。

DECLARE @TT TABLE 
(
Id int,
Name varchar(50),
Parent int
)

INSERT @TT 
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 的路径？例如，对于 id = 5，结果为:

 Great GrandFather Thomas Bishop -> Grand Mom Elian Thomas Wilson -> Aunt Nancy Manor

Answer 1

试试这个 -

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

INSERT @temp (Id, Name, Parent) 
VALUES
    (1, 'Great GrandFather Thomas Bishop', NULL),
    (2, 'Grand Mom Elian Thomas Wilson' , 1),
    (3, 'Dad James Wilson',2),
    (4, 'Uncle Michael Wilson', 2),
    (5, 'Aunt Nancy Manor', 2),
    (6, 'Grand Uncle Michael Bishop', 1),
    (7, 'Brother David James Wilson', 3),
    (8, 'Sister Michelle Clark', 3),
    (9, 'Brother Robert James Wilson', 3),
    (10, 'Me Steve James Wilson', 3) 

;WITH cte AS 
(
    SELECT *, t = 1
    FROM @temp
    WHERE Id = 5 -- <-- 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, '')