sql - 从两个表到单个 View 的 T-SQL 层次结构

Question

您好，我正在尝试创建一个 View ，该 View 从单个表中为每个用户保存(黑手党)层次结构(最多 5 级)，并使用一个附加表来填写每个用户的名称，这就是实际结构，我使用的是windows azure。

              [ 1 ]
             /  |  \
        [ 2 ]  [3]  [4]
        / |  \      |  \
     [5] [11] [12] [10]  [9]
      /\                   \
    [6] [8]                [15]
   /    /\                 /  \
[7]  [13] [14]         [17]    [16]
      |   |
     [19] [18]
     / \
  [20] [21]

这些是我从中获取数据的表:

dbo.userFamily                  dbo.user
id | user_id | parent_id        id_user   |   name
------------------------        ------------------
1  | 2       | 1                1         |   Victor
2  | 3       | 1                2         |   Lucifer
3  | 4       | 1                3         |   Hellboy
4  | 11      | 2                4         |   Spiderman
5  | 12      | 2                5         |   Martin
6  | 10      | 4                6         |   Superwoman
7  | 9       | 4                7         |   Lex Luther
8  | 15      | 9                8         |   GodMaster
9  | 17      | 15               9         |   Demon
10 | 16      | 15               10        |   balou
11 | 6       | 5                11        |   Superman
12 | 8       | 5                12        |   xman
13 | 7       | 6                13        |   hulk
14 | 13      | 8                14        |   ironman
15 | 14      | 8                15        |   aquaman
16 | 19      | 13               16        |   wonderwoman
17 | 20      | 19               17        |   batman
18 | 21      | 19               18        |   robin
19 | 18      | 14               19        |   tiger
20 | 1       | NULL             20        |   oscar

这是我到目前为止所拥有的部分:

 SELECT dbo.UserFamily.id, dbo.UserFamily.user_id, dbo.[User].name AS capo,
 dbo.UserFamily.parent_id AS Capo_id, NULL AS underboss, 
 NULL AS underboss_id, NULL AS consigliere, NULL AS consigliere_id, 
 NULL AS godfather, NULL AS godfather_id
 FROM dbo.UserFamily 
 INNER JOIN dbo.[User] ON dbo.UserFamily.parent_id = dbo.[User].id_user
 ORDER BY dbo.UserFamily.user_id

这是我尝试制作的 View (不知何故 user_id 1 丢失了，我认为这与没有parent_id 的事实有关):

id | user_id | capo       | capo_id | underboss | underboss_id | etc.     | etc.
-----------------------------------------------------------------------------
1  | 2       | Victor     | 1       | NULL      | NULL         | NULL
2  | 3       | Victor     | 1       | NULL      | NULL         | NULL
3  | 4       | Victor     | 1       | NULL      | NULL         | NULL
11 | 6       | Martin     | 5       | Lucifer   | 2            | Ewald
13 | 7       | Superwoman | 6       | Martin    | 5            | Lucifer  | 2
12 | 8       | Martin     | 5       | Martin    | 5            | Lucifer  | 2

但是我如何获得其他的呢？比如从二 Handlebars 一直到教父？这是家庭结构:

soldier (the user)
capo
underboss
consigliere
godfather

Answer 1

递归 CTE 是构建此类层次结构的技巧。这是将层次结构呈现为字符串的一个很酷的技巧:

;WITH cteBosses AS
(
    SELECT id_user, CAST(null AS VARCHAR(100)) AS parent_name, uf.parent_id, 
        name, 1 AS level, cast(name AS VARCHAR(MAX)) AS hierarchy
    FROM [dbo.user] u
    INNER JOIN dbo.userFamily uf ON u.id_user = uf.user_id
    WHERE uf.parent_id IS NULL
    UNION ALL
    SELECT u.id_user, CAST(c.name AS VARCHAR(100)) AS parent_name, uf.parent_id, 
        u.name, c.level + 1 AS level, cast(c.hierarchy + ' -> ' + u.name AS VARCHAR(MAX)) AS hierarchy
    FROM [dbo.user] u
    INNER JOIN dbo.userFamily uf ON u.id_user = uf.user_id
    INNER JOIN cteBosses c ON uf.parent_id = c.id_user
)
SELECT *
FROM cteBosses
ORDER BY hierarchy

输出:

id_user parent      name         parent_id   level  hierarchy
1       NULL        Victor       1           1      Victor
3       Victor      Hellboy      1           2      Victor -> Hellboy
2       Victor      Lucifer      2           2      Victor -> Lucifer
5       Lucifer     Martin       5           3      Victor -> Lucifer -> Martin
8       Martin      GodMaster    8           4      Victor -> Lucifer -> Martin -> GodMaster
13      GodMaster   hulk         13          5      Victor -> Lucifer -> Martin -> GodMaster -> hulk
19      hulk        tiger        19          6      Victor -> Lucifer -> Martin -> GodMaster -> hulk -> tiger
etc.

阅读 Steve Stedman 的“Common Table Expressions Joes 2 Pros”，了解许多像这样的精彩 CTE 示例。 http://stevestedman.com/2013/06/cte-scope/

要生成所需的输出，您可以根据需要多次将 CTE 连接到自身，如下所示:

;WITH cteBosses AS
(
    SELECT id_user, CAST(null AS VARCHAR(100)) AS parent_name, uf.parent_id, 
        name, 1 AS level, cast(name AS VARCHAR(MAX)) AS hierarchy
    FROM [dbo.user] u
    INNER JOIN dbo.userFamily uf ON u.id_user = uf.user_id
    WHERE uf.parent_id IS NULL
    UNION ALL
    SELECT u.id_user, CAST(c.name as VARCHAR(100)) AS parent_name, uf.parent_id, 
        u.name, c.level + 1 AS level, cast(c.hierarchy + ' -> ' + u.name AS VARCHAR(MAX)) AS hierarchy
    FROM [dbo.user] u
    INNER JOIN dbo.userFamily uf ON u.id_user = uf.user_id
    INNER JOIN cteBosses c ON uf.parent_id = c.id_user
)
SELECT c1.id_user, c1.name, c2.id_user as capo_id, c2.name as capo, 
    c3.id_user as underboss_id, c3.name as underboss,
    c4.id_user as next_boss_id, c4.name as next_boss
FROM cteBosses c1
LEFT JOIN cteBosses c2 ON c1.parent_id = c2.id_user
LEFT JOIN cteBosses c3 ON c2.parent_id = c3.id_user
LEFT JOIN cteBosses c4 ON c3.parent_id = c4.id_user

id_user name        capo_id capo        underboss_id    underboss   next_boss_id    next_boss
1       Victor      NULL    NULL        NULL            NULL        NULL            NULL
2       Lucifer     1       Victor      NULL            NULL        NULL            NULL
3       Hellboy     1       Victor      NULL            NULL        NULL            NULL
4       Spiderman   1       Victor      NULL            NULL        NULL            NULL
9       Demon       4       Spiderman   1               Victor      NULL            NULL
10      balou       4       Spiderman   1               Victor      NULL            NULL
15      aquaman     9       Demon       4               Spiderman   1               Victor
16      wonderwoman 15      aquaman     9               Demon       4               Spiderman
17      batman      15      aquaman     9               Demon       4               Spiderman

要回答有关添加 CurrentPosition 列的后续问题，您可以嵌套另一个 CTE 并将其用于 EXISTS 子句:

;WITH cteBosses AS
(
    SELECT id_user, CAST(null AS VARCHAR(100)) AS parent_name, uf.parent_id, 
        name, 1 AS level, cast(name AS VARCHAR(MAX)) AS hierarchy, id_user as top_parent, name as top_parent_name
    FROM [dbo.user] u
    INNER JOIN dbo.userFamily uf ON u.id_user = uf.user_id
    WHERE uf.parent_id IS NULL
    UNION ALL
    SELECT u.id_user, CAST(c.name as VARCHAR(100)) AS parent_name, uf.parent_id, 
        u.name, c.level + 1 AS level, cast(c.hierarchy + ' -> ' + u.name AS VARCHAR(MAX)) AS hierarchy,
        c.top_parent, c.top_parent_name
    FROM [dbo.user] u
    INNER JOIN dbo.userFamily uf ON u.id_user = uf.user_id
    INNER JOIN cteBosses c ON uf.parent_id = c.id_user
)
, cteHierarchy AS
(   
    SELECT c1.id_user, c1.name, c2.id_user as capo_id, c2.name as capo, 
        c3.id_user as underboss_id, c3.name as underboss,
        c4.id_user as next_boss_id, c4.name as next_boss    
    FROM cteBosses c1
    LEFT JOIN cteBosses c2 ON c1.parent_id = c2.id_user
    LEFT JOIN cteBosses c3 ON c2.parent_id = c3.id_user
    LEFT JOIN cteBosses c4 ON c3.parent_id = c4.id_user
)
select id_user, name, --*,  
case 
    when exists (select * from cteHierarchy cInner where next_boss = cOuter.name) then 'next_boss'
    when exists (select * from cteHierarchy cInner where underboss = cOuter.name) then 'underboss'
    when exists (select * from cteHierarchy cInner where capo = cOuter.name) then 'capo'
    else 'regular_dude'
end as CurrentPosition
from cteHierarchy cOuter

id_user   name      CurrentPosition
1         Victor    next_boss
4         Spiderman next_boss
9         Demon     underboss

您可以像这样不断添加 CTE 级别 - CTE 可以引用位于其之前(但不能位于其之后)的 CTE。因此，您基本上是为查询输出提供表别名，然后使用 EXISTS 来计算 CurrentPosition 值。