显示 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     | <------------------------

等。

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子句。看起来这是最初尝试的一些遗留物，我忘了考虑。.

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

请注意，"级别"在这里有不同的含义：级别 NULL 表示无父级，级别 0 表示子级-父级记录，级别 1 表示子级-祖父母记录等。

另请注意，就 sql 服务器中递归函数的嵌套级别而言，存在限制。我认为是32。如果您的树深度超出该范围，那么我提出的解决方案将不起作用。

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