Início / dba / Perguntas / 138928
Accepted
markokstate
Asked: 2016-05-19 12:39:46 +0800 CST

CTE recursivo para encontrar Total para todos os filhos

  • 772

Aqui está uma árvore de montagem que desejo pesquisar usando uma T-SQLconsulta recursiva (presumivelmente CTE) com os resultados esperados abaixo. Eu quero saber o valor total por montagem dada qualquer peça.

Ou seja, se eu pesquisar 'Rivet', quero saber a contagem total em cada nível da montagem, não apenas a contagem direta de filhos.

Assembly (id:1)
    |
    |-Rivet
    |-Rivet
    |-SubAssembly (id:2)
    |   |
    |   |-Rivet
    |   |-Bolt
    |   |-Bolt
    |   |-SubSubAssembly (id:3)
    |      |
    |      |-Rivet
    |      |-Rivet
    |
    |-SubAssembly (id:4)
       |-Rivet
       |-Bolt

    DESIRED Results
    -------
    ID, Count
    1 , 6
    2 , 3
    3 , 2
    4 , 1

Atualmente, posso obter os pais diretos, mas quero saber como estender meu CTE para permitir que eu role essas informações para cima.

With DirectParents AS(
--initialization
Select InstanceID, ParentID
From Instances i 
Where i.Part = 'Rivet'

UNION ALL
--recursive execution
Select i.InstanceID, i.ParentID
From PartInstances i  INNER JOIN DirectParents p
on i.ParentID = p.InstanceID

)

select ParentID, Count(instanceid) as Totals
from DirectParents
group by InstanceID, ParentID

Results
-------
ID, Count
1 , 2
2 , 2
3 , 2
4 , 1

script de criação

CREATE TABLE [dbo].[Instances] ( 
  [InstanceID] NVARCHAR (50) NOT NULL, 
  [Part] NVARCHAR (50) NOT NULL, 
  [ParentID] NVARCHAR (50) NOT NULL, );



INSERT INTO Instances 
Values 
  (1, 'Assembly', 0), 
  (50, 'Rivet', 1), 
  (50, 'Rivet', 1), 
  (2, 'SubAssembly', 1), 
  (50, 'Rivet', 2), 
  (51, 'Bolt', 2), 
  (51, 'Bolt', 2), 
  (3, 'SubSubAssembly', 2), 
  (50, 'Rivet', 3), 
  (50, 'Rivet', 3), 
  (4, 'SubAssembly2', 1), 
  (50, 'Rivet', 4), 
  (51, 'Bolt', 4)
sql-server t-sql

2 respostas

  1. Best Answer

    Este CTE recursivo ( SQL Fiddle ) deve funcionar com sua amostra:

    WITH cte(ParentID) AS(
    SELECT ParentID FROM @Instances WHERE [Part] = 'Rivet'
    UNION ALL
    SELECT i.ParentID FROM cte c
    INNER JOIN @Instances i ON c.ParentID = i.InstanceID
    WHERE i.ParentID > 0
)
SELECT ParentID, count(*) 
FROM cte
GROUP BY ParentID
ORDER BY ParentID
;

    Resultado

    ParentID    Count
1           6
2           3
3           2
4           1

    Nota: Você mencionou nos comentários que a questão contém apenas uma tabela de amostra simplificada e os dados reais têm índices adequados e lidam com duplicatas e dados adequadamente.

    Dados usados ​​( SQL Fiddle ):

    DECLARE @Instances TABLE( 
    [InstanceID] int NOT NULL
    , [Part] NVARCHAR (50) NOT NULL
    , [ParentID] int NOT NULL
);

INSERT INTO @Instances([InstanceID], [Part], [ParentID])
VALUES 
    (1, 'Assembly', 0)
    , (50, 'Rivet', 1)
    , (50, 'Rivet', 1)
    , (2, 'SubAssembly', 1)
    , (50, 'Rivet', 2)
    , (51, 'Bolt', 2)
    , (51, 'Bolt', 2)
    , (3, 'SubSubAssembly', 2)
    , (50, 'Rivet', 3)
    , (50, 'Rivet', 3)
    , (4, 'SubAssembly2', 1)
    , (50, 'Rivet', 4)
    , (51, 'Bolt', 4)
;
    • 17

  2. Não tenho certeza se entendi o que você quer dizer com "quantia" e de onde a tabela (?) PartInstances e colunas id e count vêm em sua amostra, mas calculei o que acho de seus dados de amostra.

    ;with ins as (
select [InstanceID], [Part],[ParentID],0 lvl
from instances where ParentID=0
union all
select i.[InstanceID], i.[Part],i.[ParentID], lvl+1
from instances i 
inner join ins on i.parentid=ins.InstanceID
)
select InstanceID,part,COUNT(*) cnt
from ins
group by instanceid,part

    Espero que isso lhe dê algumas idéias.

    Atualizar

    Eu entendo que este é um exemplo de teste, mas seus dados quebram tudo a partir de 1NF. Muito provavelmente sua tabela deve ser dividida em duas e normalizada.

    • -1

relate perguntas