Início / dba / Perguntas / 22704
Accepted
Jeff Sacksteder
Asked: 2012-08-18 18:26:09 +0800 CST

Construir uma junção de três tabelas com uma tabela recusiva no meio?

  • 772

Eu tenho três tabelas relevantes: Parts, PartGroup e MarkupGroup.

As peças são simples.

PartID          artificial primary key
Part            part number
PartGroupID     Foreign key

dados de amostra:

1   T1000           5
2   wizbang gold    17
3   flux capacitor  2

PartGroup é um gráfico direcionado acíclico (árvore) modelado com uma chave pai auto-vinculada

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

Os dados de amostra ficariam assim-

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 é aplicar um fator de marcação a vários PartGroups como um conjunto.

MarkupGroupID   primary key
MarkupFactor    numeric attribute field

dados de amostra-

1   15
2   20
3   25
4   22

Preciso escrever uma consulta que retorne o valor de marcação apropriado para cada peça na tabela de peças. Nunca há um caso em que mais de uma marcação pode ser aplicada a uma peça. Preciso recursar o gráfico para encontrar a marcação e não saberei no momento da consulta quantos níveis preciso recursar para encontrar uma marcação não nula.

Nunca há um caso em que uma peça encontre mais de uma junção possível para uma margem no caminho para cima da árvore, portanto, não há acúmulo que precise ser feito.

O t1000 é do tipo xyz, que possui uma chave estrangeira para um MarginGroup, então podemos juntar e obter um valor. Ele também tem um pai, mas isso é irrelevante, pois temos o valor de que precisamos.

Wizbang Gold é um software de grupo gama, que não possui chave estrangeira, mas o 'software' do nó pai possui e devemos retorná-lo.

Um capacitor de fluxo é um componente e possui uma chave estrangeira direta para um MarginGroup. Devolva esse valor.

Então os resultados seriam: 

1   T1000           22
2   wizbang gold    20
3   flux capacitor  15

Tenho certeza de que vou precisar de um CTE recursivo e um pouco de APPLY para conseguir isso, mas meu cérebro não está funcionando muito bem no momento. Se não fosse pela natureza recursiva da tabela intermediária, isso seria bastante direto. A plataforma é MS-SQL.

sql-server join

1 respostas

  1. Best Answer

    Você pode fazer a recursão em um CTE de cima para baixo carregando MarkupGroupIDcom você.

    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 Fiddle

    A CTE recursiva criará uma tabela derivada semelhante a esta.

    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

    Se você precisar de peças com partgroup não conectadas a uma marcação, poderá usar uma junção externa MarkupGroupem sua consulta principal.

    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

relate perguntas