sql - 在 SQL Hierarchy CTE 中显示所有子级和孙级

Question

我在 SQL Server 中有一个层次结构，有多个父级，但似乎无法获得我需要的结果集。

这是我到目前为止。

DECLARE @Table TABLE (ChildId varchar(max), ParentId varchar(max))
INSERT INTO @Table (ChildId,ParentId)
VALUES  
        ('England',NULL),
        ('Cities',NULL),
        ('Towns',NULL),
        ('South West','England'),
        ('Bristol','South West'),
        ('Bristol','Cities'),
        ('Suburb','Bristol'),
        ('Thornbury','South West'),
        ('Thornbury','Towns');


WITH CTE (ChildId, ParentId, Level)
AS ( 
        SELECT 
            ChildId,
            ParentID,
            0
        FROM @Table 
        WHERE ParentID IS NULL
        UNION ALL

        SELECT 
            r.ChildId,
            r.ParentId,
            ct.Level + 1
        FROM @Table r
        JOIN CTE ct
        ON ct.ChildId = r.ParentId

    )

SELECT * FROM CTE order by childId, level

这给了我这个结果集:

ChildId    | ParentId   | Level
Bristol    | Cities     | 1
Bristol    | South West | 2
Suburb     | Bristol    | 2
Suburb     | Bristol    | 3
Cities     | NULL       | 0
England    | NULL       | 0
South West | England    | 1
Thornbury  | Towns      | 1
Thornbury  | South West | 2
Towns      | NULL       | 0

但我也想要祖 parent 、曾祖 parent 和曾曾祖 parent (等):

ChildId    | ParentId   | Level
Bristol    | Cities     | 1
Bristol    | South West | 2
Bristol    | England    | <------------------------
Suburb     | South West | <------------------------ 
Suburb     | England    | <------------------------
Suburb     | Cities     | <------------------------

等等。

Answer 1

您尝试做的事情至少在某种程度上类似于 Ranganathan 的分类。在这种情况下，您必须在层次结构中向上，而不是向下:

with cte as (
    select t.ChildId, t.ParentId, 0 as [Lvl]
    from @Table t
    where t.ParentId is not null
    union all
    select c.ChildId, t.ParentId, c.Lvl + 1
    from @Table t
        inner join cte c on c.ParentId = t.ChildId
    where t.ParentId is not null
)
select * from cte c order by c.ChildId, c.Lvl, c.ParentId;

编辑:更新了 CTE 递归部分中的 WHERE 子句。看起来这是最初尝试的一些剩余物，我忘记考虑了。