IdentifiantMot de passe
Mot de passe oublié ?

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Discussion :

# Calcul des noeuds d'un arbre

Sujet :

## Contribuez Oracle

1. Calcul des noeuds d'un arbre
Bonjour,

Je vous propose un nouvel élément à utiliser : culcul des noeuds d'un arbre

culcul des noeuds d'un arbre

Qu'en pensez-vous ?

2. http://sqlfiddle.com/#!4/1210c6/74

 Code : Sélectionner tout - Visualiser dans une fenêtre à part
```12345678910111213141516171819202122232425262728293031323334353637383940414243444546
with tab as
(
select 'A' pere, 'B' fils, null valeur from dual union
select 'A', 'C', null from dual union
select 'B', 'D', null from dual union
select 'B', 'E', null from dual union
select 'D', 'H', null from dual union
select 'E', 'I', null from dual union
select 'E', 'J', null from dual union
select 'J', null, 8 from dual union
select 'I', null, 15 from dual union
select 'H', null, 32 from dual union
select 'C', 'F', null from dual union
select 'F', null, 7 from dual union
select 'C', 'G', null from dual union
select 'G', 'K', null from dual union
select 'G', 'L', null from dual union
select 'L', 'M', null from dual union
select 'K', null, 2 from dual union
select 'M', null, 22 from dual
),
rec(lvl, path, root, pere, fils, valeur, is_leaf) as
(
select level, sys_connect_by_path(pere,'/'), connect_by_root pere as root, pere, fils, nvl(valeur,0),
connect_by_isleaf
from tab
select pere from tab t1 where
not exists(select t2.fils from tab t2 where t2.fils = t1.pere)
)
connect by prior fils = pere
),
rec_dep as (
select distinct r1.path as path, r2.path as depending_on, r2.valeur, r2.is_leaf, r1.lvl from rec r1
join rec r2 on instr(r2.path, r1.path, 1,1) > 0 and r1.path <> r2.path
),
results as (
select substr(path,instr(path,'/',-1)+1) as node, path, sum(valeur) as valeur
from rec_dep group by path
union
select substr(depending_on,instr(depending_on,'/',-1)+1), depending_on, valeur from rec_dep
where depending_on not in (select path from rec_dep) and is_leaf = 1
and regexp_count(path,'/') = regexp_count(depending_on,'/')-1
)
select * from results order by node;```

 Actualités F.A.Q ORACLE TUTORIELS ORACLE TUTORIELS SQL SCRIPTS SQL LIVRES ORACLE QUIZ