sql - 返回自身及其所有后代的递归查询

Question

我在 SQL Server 2005 数据库中的表“级别”如下所示:

LevelId Description      ParentLevelId
0       Level_1          NULL
1       Level_2          0
2       Level_3          0
3       Level_4          1
4       Level_5          3

我现在想创建一个查询，该查询将生成一个结果集，其中返回每个级别及其所有子级、孙级等。所以基本上我想返回这样的输出:

LevelId Description DescendantId Descendant_Description
0       Level_1     0            Level_1
0       Level_1     1            Level_2
0       Level_1     2            Level_3    
0       Level_1     3            Level_4   
0       Level_1     4            Level_5    
1       Level_2     1            Level_2
1       Level_2     3            Level_3
1       Level_2     4            Level_5
2       Level_3     2            Level_3
3       Level_4     3            Level_4
4       Level_5     4            Level_5 

奇怪的是，我今天写了一个类似的查询，其中显示了所有级别及其所有祖先。不知何故，我一直在为“相反”的东西写一个类似的查询。有人有什么想法吗？

Answer 1

WITH    q (LevelId, Description, DescendantId, Descendant_Description) AS
        (
        SELECT  LevelId, Description, LevelId, Description
        FROM    mytable
        UNION ALL
        SELECT  t.LevelId, t.Description, q.DescendantId, q.Descendant_Description
        FROM    q
        JOIN    mytable t
        ON      t.ParentLevelId = q.LevelId
        )
SELECT  *
FROM    q
ORDER BY
        LevelId, DescendantId

由于此查询返回系统中的所有祖先-后代对(构建了一个所谓的传递闭包)，您需要反过来说就是交换字段并更改顺序:

WITH    q (LevelId, Description, DescendantId, Descendant_Description) AS
        (
        SELECT  LevelId, Description, LevelId, Description
        FROM    mytable
        UNION ALL
        SELECT  t.LevelId, t.Description, q.DescendantId, q.Descendant_Description
        FROM    q
        JOIN    mytable t
        ON      t.ParentLevelId = q.LevelId
        )
SELECT  DescendantId AS LevelId, Descendant_Description AS Description,
        LevelId AS DescendantId, Description AS Descendant_Description
FROM    q
ORDER BY
        LevelId, DescendantId