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
| With tp as (select 'objet_1' OBJ ,'A' C1,null C2, null C3 from dual union all
select 'objet_2' ,'A' ,'Z' , null from dual union all
select 'objet_3' ,'B' ,'W' , null from dual union all
select 'objet_4' ,'C' ,'Z' , null from dual union all
select 'objet_5' ,'D' ,'Y' , null from dual union all
select 'objet_6' ,'D' ,'U' , 'O' from dual union all
select 'objet_7' ,'E' ,'Z' , 'P' from dual union all
select 'objet_8' ,'F' ,'X' , 'P' from dual union all
select 'objet_9' ,'G' ,'Y' , null from dual union all
select 'objet_10' ,'H' ,'U' , null from dual ),
t as (select rownum rn, tp.* from tp ),
t2 as (select distinct OBJ
, C1
, C2
, C3
, prior C1 PC1
, prior C2 PC2
, prior C3 PC3
, prior OBJ POB
from t
connect by (C1=prior C1
or C2=prior C2
or C3=prior C3 )
and rn>prior RN ),
t3 as (select rownum GN, OBJ
from ( select OBJ, max(PC1), max(PC2), max(PC3) from t2
HAVING max(PC1) is null and max(PC1) is null and max(PC3) is null
group by OBJ, C1, C2, C3
order by OBJ )),
t4 as (select OBJ, CONNECT_BY_ROOT OBJ TOBJ
from t2
start with obj in ( select OBJ from t3 )
connect by prior OBJ=POB)
select distinct t4.obj
, t3.gn
from t3
, t4
where t3.obj = t4.tobj
order by gn |
Partager