create table TreeTable(
ID number(2) primary key,
ParentID references TreeTable,
name varchar2(20),
Val number(1));
insert into TreeTable values( 1,null,'Big Boss' ,null);
insert into TreeTable values( 2, 1,'VP Marketing' ,null);
insert into TreeTable values( 9, 2,'Susan' ,null);
insert into TreeTable values(10, 2,'Sam' ,2);
insert into TreeTable values(11, 2,'VP Marketing Easy',null);
insert into TreeTable values(12, 11,'A' ,null);
insert into TreeTable values(13, 11,'B' ,null);
insert into TreeTable values( 3, 1,'VP Sales' ,null);
insert into TreeTable values( 4, 3,'Joe' ,null);
insert into TreeTable values( 5, 4,'Bill' ,5);
insert into TreeTable values( 6, 1,'VP Engineering' ,null);
insert into TreeTable values( 7, 6,'Jane' ,null);
insert into TreeTable values( 8, 6,'Bob' ,3);
insert into TreeTable values(20, 1,'Abcdefg' ,null);
insert into TreeTable values(21, 20,'hijklmn' ,null);
insert into TreeTable values(22, 21,'opqrstu' ,null);
insert into TreeTable values(30, 1,'30Level2' ,null);
insert into TreeTable values(31, 30,'30Level3' ,7);
insert into TreeTable values(32, 30,'30Level3' ,null);
insert into TreeTable values(35, 31,'30Level4' ,null);
insert into TreeTable values(40, 1,'40Level2' ,null);
insert into TreeTable values(41, 40,'40Level3' ,null);
insert into TreeTable values(42, 40,'40Level3' ,8);
insert into TreeTable values(45, 41,'40Level4' ,null);
commit;
col path for a60
--■■■深さ優先探索の探索順序を使う方法1■■■
select path
from (select path,Rn,
max(case when LV = 1 then 1
when isLeaf = 1 and Val is not null then 1 end)
over(partition by groupID) as willOut
from (select path,Val,isLeaf,LV,Rn,
Last_Value(decode(LV,2,ID) ignore nulls)
over(order by Rn Rows Unbounded preceding) as groupID
from (select path,Val,isLeaf,LV,ID,RowNum as Rn
from (select sys_connect_by_path(name, ' / ') as path,
Val,connect_by_isleaf as isLeaf,
Level as LV,ID
from TreeTable
connect by Prior ID = ParentID
Start With ParentID is null
order siblings by ID))))
where willOut = 1
order by Rn;
--■■■深さ優先探索の探索順序を使う方法2■■■
select path
from (select path,Rn,
max(case when LV = 1 then 1
when isLeaf = 1 and Val is not null then 1 end)
over(partition by groupID) as willOut
from (select path,Val,isLeaf,LV,RowNum as Rn,
Last_Value(decode(LV,2,ID) ignore nulls)
over(order by RowNum Rows Unbounded preceding) as groupID
from (select sys_connect_by_path(name, ' / ') as path,
Val,connect_by_isleaf as isLeaf,
Level as LV,ID
from TreeTable
connect by Prior ID = ParentID
Start With ParentID is null
order siblings by ID)))
where willOut = 1
order by Rn;