建立一個三表連接，中間有一個遞歸表？

我有三個相關的表：Parts、PartGroup 和 MarkupGroup。

零件很簡單。

PartID          artificial primary key
Part            part number
PartGroupID     Foreign key

樣本數據：

1   T1000           5
2   wizbang gold    17
3   flux capacitor  2

PartGroup 是使用自連結父鍵建模的無環有向圖（樹）

PartGroupID     artificial primary key
Description     name of group
ParentID        foreign key linked to PartGroupID
MarkupGroupID   foreign key linked to MarkupGroup

樣本數據看起來像這樣 -

1   system      null    null
2   component   null    1 
3   software    null    2
4   abc         1       3
5   xyz         1       4
6   123         4       null
7   456         4       null
8   789         5       null
9   a1          6       null
10  b2          6       null
11  c3          7       null
12  d4          7       null
13  e5          8       null  
14  f6          8       null
15  alpha       3       null
16  beta        3       null
17  gamma       3       null

MarkupGroup 是將一個標記因子應用到幾個 PartGroup 作為一個集合。

MarkupGroupID   primary key
MarkupFactor    numeric attribute field

樣本數據-

1   15
2   20
3   25
4   22

我需要編寫一個查詢，為元件表中的每個元件返回適當的標記量。永遠不會有多個標記可以應用於零件的情況。我需要遞歸圖表以找到標記，並且在查詢時我不知道需要遞歸多少級別才能找到非空標記。

永遠不會有零件在向上樹的過程中遇到多個可能的連接到邊緣的情況，因此不需要進行累積。

t1000 是 xyz 類型，它有一個 MarginGroup 的外鍵，所以我們可以加入並獲取一個值。它也有一個父母，但這無關緊要，因為我們有我們需要的價值。

Wizbang Gold 是 gamma-group 軟體，它沒有外鍵，但父節點“軟體”有，我們應該返回它。

磁通電容器是一個組件，它具有指向 MarginGroup 的直接外鍵。返回該值。

所以結果將是：

1   T1000           22
2   wizbang gold    20
3   flux capacitor  15

我很確定我將需要一個遞歸 CTE 和一些 APPLY 的味道來獲得這個，但我的大腦目前工作不太好。如果不是因為中間表的遞歸性質，這將非常簡單。平台是 MS-SQL。

您可以隨身攜帶自上而下在 CTE 中進行遞歸MarkupGroupID。

with C as
(
 select P.PartGroupID,
        P.ParentID,
        P.MarkupGroupID
 from PartGroup as P
 where P.ParentID is null
 union all 
 select P.PartGroupID,
        P.ParentID,
        coalesce(P.MarkupGroupID, C.MarkupGroupID)
 from PartGroup as P 
   inner join C 
     on P.ParentID = C.PartGroupID
)
select P.PartID,
      P.Part,
      MG.MarkupFactor
from Parts as P
 inner join C
   on P.PartGroupID = C.PartGroupID
 inner join MarkupGroup as MG
   on C.MarkupGroupID = MG.MarkupGroupID
order by P.PartID

SQL小提琴

遞歸 CTE 將創建一個如下所示的派生表。

PartGroupID ParentID    MarkupGroupID
----------- ----------- -------------
1           NULL        NULL
2           NULL        1
3           NULL        2
15          3           2
16          3           2
17          3           2
4           1           3
5           1           4
8           5           4
13          8           4
14          8           4
6           4           3
7           4           3
11          7           3
12          7           3
9           6           3
10          6           3

如果您需要未連接到標記的部分組的部分，您可以MarkupGroup在主查詢中使用外連接。

with C as
(
 select ...
)
select P.PartID,
      P.Part,
      MG.MarkupFactor
from Parts as P
 inner join C
   on P.PartGroupID = C.PartGroupID
 left outer join MarkupGroup as MG
   on C.MarkupGroupID = MG.MarkupGroupID
order by P.PartID

引用自：https://dba.stackexchange.com/questions/22704