1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
| create table ArticleComposant
( Article varchar(10)
, Composant varchar(10)
, Qte decimal(5,3)
);
insert into ArticleComposant values
('03330' , 'MP1' , 0.5),
('03330' , 'MP2' , 0.4),
('03330' , 'I3' , 0.1),
('I3' , 'MP4' , 0.8),
('I3' , 'MP2' , 0.2),
('033320', '03330', 20.0);
with cte_recurs (ArticleRoot, Article, Composant, Qte, lvl) as
(
select Article, Article, Composant, Qte, 1
from ArticleComposant
where Article = '033320'
union all
select cte.ArticleRoot
, acp.Article
, acp.Composant
, cast(cte.Qte * acp.Qte as decimal(5,3))
, cte.lvl + 1
from cte_recurs as cte
join ArticleComposant as acp on cte.Composant = acp.Article
where cte.lvl < 10
)
select ArticleRoot, Composant
, sum(Qte) as Qte
, count(*) as presence
from cte_recurs
group by ArticleRoot, Composant;
ArticleRoot Composant Qte Presence
----------- -------- ----- --------
033320 03330 20.000 1
033320 I3 2.000 1
033320 MP1 10.000 1
033320 MP2 8.400 2
033320 MP4 1.600 1 |
