在SQL层次结构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

但是我也希望有祖父母、曾祖父母、高祖父母等（等等）：

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

etc.

- Paul Grimshaw

---

可能是如何从SQL表中检索分层数据？的重复问题。请查看该QA中所选答案。这可能是您使用CTE所寻找的内容。 - Bernd Linde

不，那是一个标准的层次结构SQL问题，有很多例子。我特别需要所有链接（孙子，曾孙）就像问题所述 - 请删除重复标志。 - Paul Grimshaw

可能是选择语句以返回父级和无限子级的重复问题。 - Tanner

那个问题也不同，实际上接受的答案已经在我的问题中了，关系是我的级别。我正在寻找更多结果，针对不同的关系（子代、孙子等）分开行显示。 - Paul Grimshaw

Answer 1

您正在尝试做的事情，至少在某种程度上类似于朗格纳森分类法。在这种情况下，您需要向上提升层级，而不是向下：

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子句。看起来这是初始尝试留下的一些遗漏，我忘记仔细思考了...

- Roger Wolf

---

干得好！比我的解决方案更简洁，可能也更有效率。不过我不明白 where 子句中 c.ParentId != t.ParentId 的目的是什么。 - Giorgos Betsos

@GiorgosBetsos，你是对的。我更新了答案。 - Roger Wolf

Answer 2

使用递归表值函数代替公用表达式（CTE）怎么样？

CREATE FUNCTION tvf_GetParents 
(   
    @childID VARCHAR(MAX),
    @level INT
)
RETURNS 
@output TABLE 
(
    ancestor VARCHAR(MAX),
    level INT
)
AS
BEGIN

    DECLARE @parentIDs TABLE (pID VARCHAR(MAX))

    -- Get parent of child and add it to output
    IF EXISTS (SELECT 1 FROM HTable WHERE ChildId = @childID AND ParentId IS NOT NULL)
    BEGIN
       INSERT @parentIDs
       SELECT ParentId FROM HTable WHERE ChildId = @childID

       INSERT INTO @output (ancestor, level)
       SELECT pID, @level FROM @parentIDs
    END    
    ELSE
       RETURN

    DECLARE @pID VARCHAR(MAX) = 0

    -- Iterate over all parents (cursorless loop)
    WHILE (1 = 1) 
    BEGIN  

        -- Get next ParentId
        SELECT TOP 1 @pID = pID
        FROM @parentIDs
        WHERE pID > @pID 
        ORDER BY pID

        -- Exit loop if no more parents
        IF @@ROWCOUNT = 0 BREAK;

        -- call function recursively so as to add to output 
        -- the rest of the ancestors (if any)
        INSERT INTO @output (ancestor, level)
        SELECT ancestor, level FROM tvf_GetParents(@pID, @level + 1) 
    END

    RETURN 
END
GO

使用上述函数，您可以轻松获取所有子 - 祖先对：

SELECT DISTINCT ChildId, ancestor, level
FROM HTable h 
OUTER APPLY tvf_GetParents(h.ChildId, 0) AS p
ORDER BY ChildId, Level

输出：

ChildId    ancestor     level
------------------------------
Bristol    Cities       0
Bristol    South West   0
Bristol    England      1
Cities     NULL         NULL
England    NULL         NULL
South West England      0
Suburb     Bristol      0
Suburb     Cities       1
Suburb     South West   1
Suburb     England      2
Thornbury  South West   0
Thornbury  Towns        0
Thornbury  England      1
Towns      NULL         NULL

请注意，这里的“Level”有不同的含义：level NULL表示没有父级的子级，level 0表示子父记录，level 1表示子祖父记录等等。

另请注意，就SQL Server中递归函数的嵌套级别而言，存在限制。我认为它是32。如果您的树深度超出了该范围，则我提出的解决方案将无法使用。

- Giorgos Betsos

---

谢谢，但我更喜欢CTE版本，更简洁。@Roger Wolf已经给出了一个解决方案。 - Paul Grimshaw

Answer 3

这是你要找的吗？

WITH CTE (ChildId, FirstChild, ParentId, Level)
AS ( 
        SELECT 
            ChildId,
            ChildId as FirstChild,
            ParentID,
            0
        FROM @Table 
        WHERE ParentID IS NULL
        UNION ALL
        SELECT 
            r.ChildId,
            ct.FirstChild,
            r.ParentId,
            ct.Level + 1
        FROM @Table r
        JOIN CTE ct
        ON ct.ChildId = r.ParentId

    )
SELECT ChildId,
    ParentId,
    Level
FROM CTE
UNION
SELECT FirstChild,
    ParentId,
    Level
FROM CTE
ORDER BY ChildId,
    Level,
    ParentId

输出：

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

- Bacon Bits

---

我认为上面的输出不正确：例如，英格兰是一个顶级记录，应该只在输出中出现一次。 - Giorgos Betsos