siblingsT
ID OyaID
-- -----
1 null
2 1
3 1
4 1
5 3
6 3
7 4
8 4
9 6
10 7
20 null
21 20
22 20
23 21
24 21
階層問い合わせを使った下記のクエリと同じ結果を取得する。
select connect_by_root ID as treeID,
ID,OyaID,Level,
sys_connect_by_path(to_char(ID),',') as path
from siblingsT
start with OyaID is null
connect by prior ID = OyaID
order siblings by ID desc;
出力結果
treeID ID OyaID Level path
------ -- ----- ----- ---------
20 20 null 1 ,20
20 22 20 2 ,20,22
20 21 20 2 ,20,21
20 24 21 3 ,20,21,24
20 23 21 3 ,20,21,23
1 1 null 1 ,1
1 4 1 2 ,1,4
1 8 4 3 ,1,4,8
1 7 4 3 ,1,4,7
1 10 7 4 ,1,4,7,10
1 3 1 2 ,1,3
1 6 3 3 ,1,3,6
1 9 6 4 ,1,3,6,9
1 5 3 3 ,1,3,5
1 2 1 2 ,1,2
create table siblingsT(
ID number primary key,
OyaID number);
insert into siblingsT values( 1,null);
insert into siblingsT values( 2, 1);
insert into siblingsT values( 3, 1);
insert into siblingsT values( 4, 1);
insert into siblingsT values( 5, 3);
insert into siblingsT values( 6, 3);
insert into siblingsT values( 7, 4);
insert into siblingsT values( 8, 4);
insert into siblingsT values( 9, 6);
insert into siblingsT values(10, 7);
insert into siblingsT values(20,null);
insert into siblingsT values(21, 20);
insert into siblingsT values(22, 20);
insert into siblingsT values(23, 21);
insert into siblingsT values(24, 21);
commit;
col path for a10
with rec(treeID,ID,OyaID,LV,path) as(
select ID,ID,OyaID,1,',' || to_char(ID)
from siblingsT
where OyaID is null
union all
select a.treeID,b.ID,b.OyaID,a.LV+1,a.path || ',' || to_char(b.ID)
from rec a,siblingsT b
where a.ID = b.OyaID)
search depth first by ID desc set SortKey
select * from rec
order by SortKey;