SQL Server T-SQL Recursive Query to show nested Tree structure

ujv3wf0j 于 2023-03-07 发布在其他

关注(0)|答案(2)|浏览(155)

I have the following 2 tables:

CREATE TABLE [Names] 
    (
    [Id] INT PRIMARY KEY,
    [Name] VARCHAR(100)
    )

CREATE TABLE [Relationships]
    (
    [Parent] [int] REFERENCES [Names]([Id]), 
    [Child] [int] REFERENCES [Names]([Id])
    )

Sample Data:

INSERT [NAMES] VALUES (1,'FRANK')
INSERT [NAMES] VALUES (2,'JO')
INSERT [NAMES] VALUES (3,'MARY')
INSERT [NAMES] VALUES (4,'PETER')
INSERT [NAMES] VALUES (5,'MAY')

INSERT [RELATIONSHIPS] VALUES (1,2)
INSERT [RELATIONSHIPS] VALUES (2,3)
INSERT [RELATIONSHIPS] VALUES (4,2)
INSERT [RELATIONSHIPS] VALUES (5,4)

How to show a nested (tree) list of names including [Id], [Name] and [Level], where [Level] indicates the nest level from the top (Root: Level = 0; First children of Root: Level = 1; etc…)? For instance, the result should be showing:

Level     Relationship
-----     ------------
2         FRANK <- JO
3         FRANK <- JO <- MARY
2         PETER <- JO
3         MAY <- PETER <- JO

sql-server

来源：https://stackoverflow.com/questions/26071319/t-sql-recursive-query-to-show-nested-tree-structure

2条答案

按热度按时间

e1xvtsh31#

You might consider switching to Hierarchical Data . TSQL supports it well enough and you don't need to 'reinvent the wheel'. This will make you queries easier in the long run.

Go here for a nice tutorial on the subject.

赞(0）回复(0）举报 2023-03-07

nhaq1z212#

Try this:

with Relatives as
(
    select n.Id, cast(n.Name as varchar(max)) Relationship, 0 [Level]
    from Names n
    where not exists
    (
        select *
        from Relationships r
        where n.Id = r.Child
    )

    union all

    select n.Id, p.Relationship + ' <- ' + n.Name Relationship, p.[Level] + 1 [Level]
    from Names n
    join Relationships r
        on n.Id = r.Child
    join Relatives p
        on r.Parent = p.Id
)

select Relationship, [Level]
from Relatives

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server T-SQL Recursive Query to show nested Tree structure

2条答案

相关问题

热门标签

最新问答