SQL Server语言 - 按层次结构顺序列出父记录

Question

我有一个表，用于构建菜单项的树结构。此表的示例内容如下：

Parent      Child
------      ------
190         192
192         180
180         185
185         184
190         191
191         197
197         200

我需要一个查询，它将按照父记录

在层次结构中出现的顺序(包括子 ID(为我提供一个逗号分隔的父记录列表，例如：

• 给定子 ID 184，它应返回 190、192、180、185、184
• 给定子 ID 200，它应返回 190、191、197、200

Answer 1

来了...

Declare @data Table
(
Parent int,
Child int
)
insert into @data values
(190, 192),
(192, 180),
(180, 185),
(185, 184),
(190, 191),
(191, 197),
(197, 200)
Declare @Id as Int = 184
/*
    CompleteData - query produce following output
        ID          ParentId
        ----------- -----------
        180         192
        184         185
        185         180
        190         NULL -- we discoverd this missing data
        191         190
        192         190
        197         191
        200         197
    ChildHierarchyData - query produce following ouput
        ID          ParentID    Level
        ----------- ----------- -----------
        184         185         0
        185         180         1
        180         192         2
        192         190         3
        190         NULL        4
    Concatinated - query conact all ID from above result
*/
;with CompleteData
as
(
    Select Child ID, Parent ParentId from @data
    UNION
    Select Child.Parent Id, Parent.Parent ParentId From @data   Child
        Left Outer Join @data Parent
            on Child.Parent = parent.Child
    WHERE
        parent.Parent IS NULL
),
ChildHierarchyData(ID,ParentID, Level)
as
(
    Select ID,ParentID, 0 as Level from CompleteData Where ID = @Id
    union all
    Select CompleteData.ID, CompleteData.ParentID, ChildHierarchyData.Level +1 from CompleteData 
        INNER Join ChildHierarchyData
            on ChildHierarchyData.ParentID = CompleteData.ID
),
Concatinated(result)
as
(
    Select Cast((select Cast(ID  as nvarchar) + ',' [data()] from ChildHierarchyData Order By Level Desc FOR XML Path('')) as Nvarchar(max))
)
select Left(result, len(result)-1) as Result from Concatinated

Answer 2

为了演示，我使用了表变量。若要使用标准表，请删除@tempTable声明并插入语句。然后将@tempTable引用替换为表名。

declare @childId int
set @childId = 184
declare @tempTable table(parent int, child int)
insert into @tempTable values(190, 192)
insert into @tempTable values(192, 180)
insert into @tempTable values(180, 185)
insert into @tempTable values(185, 184)
insert into @tempTable values(190, 191)
insert into @tempTable values(191, 197)
insert into @tempTable values(197, 200)
declare @currentItem int
set @currentItem = @childId
declare @output varchar(max)
set @output = cast(@currentItem as varchar)
while (exists(select 1 from @tempTable where child = @currentItem))
begin
    select 
        @currentItem = parent
    from 
        @tempTable 
    where 
        child = @currentItem
    set @output = cast(@currentItem as varchar) + ', ' + @output 
end
select @output

一些示例输出：

对于184：190, 192, 180, 165, 184

200：190, 191, 197, 200

Answer 3

如果您需要逗号分隔的列表，使用递归 cte 很容易做到：

with cte as (
    select
        t.Parent, 1 as Level,
        cast(t.Parent as nvarchar(max)) + ',' + cast(t.Child as nvarchar(max)) as Path
    from Table1 as t
    where t.Child = @Child
    union all
    select
        t.Parent, Level + 1 as Level,
        cast(t.Parent as nvarchar(max)) + ',' + c.Path as Path
    from Table1 as t
        inner join cte as c on c.Parent = t.Child
)
select top 1 Path
from cte
order by Level desc

SQL 小提琴演示