获取树中节点的路径 [英] Get path of node in tree

问题描述

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

I want get path of specific node in my tree. Please see my tree data.

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，结果是:

How Can I Get Path Of specific id ? for example for id = 5, result is :

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

推荐答案

试试这个 -

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, '')

这篇关于获取树中节点的路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！