connect_by_IsLeaf擬似列
ノードが木の葉かを表す
connect_by_IsLeaf疑似列は、そのノードが木の葉であれば1、木の葉でなければ0となります。
create table IsLeafSample(ID primary key,OyaID) as
select 1,null from dual union all
select 2, 1 from dual union all
select 3, 1 from dual union all
select 4, 3 from dual union all
select 5, 3 from dual union all
select 10,null from dual union all
select 11, 10 from dual;
-- connect_by_IsLeaf擬似列の使用例
select ID,OyaID,Level,connect_by_IsLeaf as IsLeaf,
sys_connect_by_path(to_char(ID),',') as Path
from IsLeafSample
start with OyaID is null
connect by prior ID = OyaID;
出力結果
ID OyaID Level IsLeaf Path
-- ----- ----- ------ ------
1 null 1 0 ,1
2 1 2 1 ,1,2
3 1 2 0 ,1,3
4 3 3 1 ,1,3,4
5 3 3 1 ,1,3,5
10 null 1 0 ,10
11 10 2 1 ,10,11
connect_by_IsLeaf疑似列のイメージは、下記となります。
木ごとに区切る赤線をイメージして、葉であるノードに緑色を塗ってます。
connect_by_IsLeaf疑似列の使用法としては、下記のSQLのように、where句でconnect_by_IsLeaf = 1を指定して、木の葉である行のみを取得することが多いです。
-- 木の葉である行のみを取得
select ID,OyaID,Level,
sys_connect_by_path(to_char(ID),',') as Path
from IsLeafSample
where connect_by_IsLeaf = 1
start with OyaID is null
connect by prior ID = OyaID;
出力結果
ID OyaID Level Path
-- ----- ----- ------
2 1 2 ,1,2
4 3 3 ,1,3,4
5 3 3 ,1,3,5
11 10 2 ,10,11
connect_by_root演算子
木の根である行の値を取得
connect_by_root演算子は、木の根である行の値を取得するのに使います。
下記のSQLでは、木の根である行のID列を、列別名rootIDとして取得しています。
-- connect_by_root演算子の使用例
select ID,OyaID,
connect_by_root ID as rootID,
sys_connect_by_path(to_char(ID),',') as Path
from IsLeafSample
start with OyaID is null
connect by prior ID = OyaID;
出力結果
ID OyaID rootID Path
-- ----- ------ ------
1 null 1 ,1
2 1 1 ,1,2
3 1 1 ,1,3
4 3 1 ,1,3,4
5 3 1 ,1,3,5
10 null 10 ,10
11 10 10 ,10,11
connect_by_root演算子のイメージは、下記となります。
木ごとに区切る赤線をイメージして、根であるノードに茶色を塗ってます。
下記のSQLのように、connect_by_root演算子を使って、木ごとに幅優先探索順で出力するといった使用法もあります。
-- 木ごとに幅優先探索順に出力
select ID,OyaID,Level,
sys_connect_by_path(to_char(ID),',') as Path
from IsLeafSample
start with OyaID is null
connect by prior ID = OyaID
order by connect_by_root ID,Level,ID;
出力結果
ID OyaID Level Path
-- ----- ----- ------
1 null 1 ,1
2 1 2 ,1,2
3 1 2 ,1,3
4 3 3 ,1,3,4
5 3 3 ,1,3,5
10 null 1 ,10
11 10 2 ,10,11
prior演算子
親の行の値を取得
prior演算子は、主にconnect by句で使われますが、select句で使用して、親の行の値を取得するといった使用法もあります。
create table PriorSample(ID primary key,OyaID,Name) as
select 1,null,'AAAA' from dual union all
select 2, 1,'BBBB' from dual union all
select 3, 1,'CCCC' from dual union all
select 4, 3,'DDDD' from dual union all
select 5, 3,'EEEE' from dual union all
select 10,null,'FFFF' from dual union all
select 11, 10,'GGGG' from dual;
-- prior演算子の使用例
select ID,OyaID,Name,prior Name as OyaName,
Level,sys_connect_by_path(to_char(ID),',') as Path
from PriorSample
start with OyaID is null
connect by prior ID = OyaID;
出力結果
ID OyaID Name OyaName Level Path
-- ----- ---- ------- ----- -------
1 null AAAA null 1 ,1
2 1 BBBB AAAA 2 ,1,2
3 1 CCCC AAAA 2 ,1,3
4 3 DDDD CCCC 3 ,1,3,4
5 3 EEEE CCCC 3 ,1,3,5
10 null FFFF null 1 ,10
11 10 GGGG FFFF 2 ,10,11
SQLのイメージは下記となります。木ごとに区切る赤線をイメージしてます。