将层次结构表中项目的所有父项列为分隔字符串 SQL [英] Listing all Parents of an items in a hierarchy table as delimitted string SQL

问题描述

我有一个这样的 SQL 表

I have an SQL table like this

ID        Name        ParentID
------------------------------
1        Alex          0
2        John          0
3        Don           1
4        Philip        2
5        Shiva         2
6        San           3
7        Antony        6
8        Mathew        2
9        Cyril         8
10       Johan         9
-------------------------

我正在寻找这样的输出

如果我通过了 ID 7,10

if I pass the ID 7,10

输出表将是

ID          Name           Relation
------------------------------------
7           Antony         Alex->Don->San->Antony
10          Johan          John->Mathew->Cyril->Johan

我如何使用 CTE 实现这一点

How can I achieve that using CTE

推荐答案

这似乎可以解决问题.关键是要意识到我们可以以倒退的方式建立路径，并在我们不再有父级定位时停止:

This seems to do the trick. The key is to realise that we can build up the path in backwards fashion and stop when we no longer have a parent to locate:

DECLARE @t table (ID int not null, Name varchar(19) not null, ParentID int null)
insert into @t(ID,Name,ParentID) values
(1 ,'Alex',null),
(2 ,'John',null),
(3 ,'Don',1),
(4 ,'Philip',2),
(5 ,'Shiva',2),
(6 ,'San',3),
(7 ,'Antony',6),
(8 ,'Mathew',2),
(9 ,'Cyril',8),
(10,'Johan',9)

declare @search table (ID int not null)
insert into @search (ID) values (7),(10)

;With Paths as (
    select s.ID as RootID,t.ID,t.ParentID,t.Name, CONVERT(varchar(max),t.Name) as Path
    from
        @search s
            inner join
        @t t
            on
                s.ID = t.ID
    union all
    select p.RootID,t.ID,t.ParentID,p.Name, t.Name + '->' + p.Path
    from Paths p
            inner join
        @t t
            on
                p.ParentID = t.ID
)
select * from Paths where ParentID is null

结果:

RootID      ID          ParentID    Name                Path
----------- ----------- ----------- ------------------- ----------------------------
10          2           NULL        Johan               John->Mathew->Cyril->Johan
7           1           NULL        Antony              Alex->Don->San->Antony

(我在额外的列中留下来帮助显示最终状态.查询 CTE 而不过滤也可能有指导意义)

(I've left in additional columns to help to show the final state. Querying the CTE without filtering may also be instructive)

我还要提醒的是，如果可能的话，我通常不会使用分隔字符串 - 当 SQL Server 具有设计用于处理多个值的类型时，这不是一个很好的表示.

I'd also caution that I wouldn't usually work with delimited strings if at all possible - it's not a great representation when SQL Server has types designed for working with multiple values.

这篇关于将层次结构表中项目的所有父项列为分隔字符串 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！