主页 / dba / 问题 / 22704
Accepted
Jeff Sacksteder
Asked: 2012-08-18 18:26:09 +0800 CST

建立一个三表连接，中间有一个递归表？

  • 772

我有三个相关的表：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。

sql-server join

1 个回答

  1. Best Answer

    您可以随身携带自上而下在 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
    • 5

相关问题