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
| with src (id, famille, pere, enfant, sexe) as
(
select 1, 'F1', 'nom1' , 'nom2' , 'M' from dual union all
select 2, 'F1', 'nom2' , 'nom3' , 'M' from dual union all
select 3, 'F1', 'nom3' , 'nom4' , 'F' from dual union all
select 4, 'F1', 'nom4' , 'nom5' , 'M' from dual union all
select 5, 'F2', 'nom11', 'nom12', 'M' from dual union all
select 6, 'F2', 'nom12', 'nom13', 'F' from dual union all
select 7, 'F2', 'nom13', 'nom14', 'M' from dual union all
select 18, 'F3', 'nom21', 'nom22', 'M' from dual
)
, cte_src as
(
-- Suppression des lignes où les enfants ne sont pas parents
select famille, pere, enfant, sexe
from src t1
where exists (select null
from src t2
where t2.pere = t1.enfant
and t2.famille = t1.famille)
)
select famille
, connect_by_root pere as root
, enfant
, sexe
from cte_src
where connect_by_isleaf = 1
start with (famille, pere) not in (select famille, enfant from src)
connect by pere = prior enfant
and famille = prior famille;
FAMILLE ROOT ENFANT SEXE
------- ----- ------ ----
F1 nom1 nom4 F
F2 nom11 nom13 F |
Partager