Level擬似列
ノードのレベルを表示
Level擬似列は、ノードのレベルを表します。
start with句の条件を満たしたノードのレベルが1となり、子供はレベル2、孫はレベル3といった感じでレベルが増えていきます。
create table LevelSample(ID,NextID) as
select 1, 2 from dual union all
select 2, 3 from dual union all
select 3, 4 from dual union all
select 3, 5 from dual union all
select 4,null from dual union all
select 5, 6 from dual union all
select 6,null from dual;
木の根となる条件を、ID = 1 親子条件を、親のNextID = 子のID として階層問い合わせを行います。
-- Level擬似列の使用例1
select ID,NextID,Level,
sys_connect_by_path(to_char(ID),',') as Path
from LevelSample
start with ID = 1
connect by prior NextID = ID;
出力結果
ID NextID Level Path
-- ------ ----- ----------
1 2 1 ,1
2 3 2 ,1,2
3 4 3 ,1,2,3
4 null 4 ,1,2,3,4
3 5 3 ,1,2,3
5 6 4 ,1,2,3,5
6 null 5 ,1,2,3,5,6
Level擬似列のイメージは下記となります。
下記のSQLのように、レベルの上限を3とした階層問い合わせを行ったり、where句でレベルが1または3の行を抽出するといったLevel擬似列の使用法もあります。
-- Level擬似列の使用例2
select ID,NextID,Level,
sys_connect_by_path(to_char(ID),',') as Path
from LevelSample
where Level in(1,3)
start with ID = 1
connect by prior NextID = ID
and Level <=3;
出力結果
ID NextID Level Path
-- ------ ----- ------
1 2 1 ,1
3 4 3 ,1,2,3
3 5 3 ,1,2,3
sys_connect_by_path関数
根からの経路を表示
sys_connect_by_path関数は、根からの経路を表します。
-- sys_connect_by_path関数の使用例
select ID,NextID,Level,
sys_connect_by_path(to_char(ID),',') as Path
from LevelSample
start with ID = 1
connect by prior NextID = ID;
出力結果
ID NextID Level Path
-- ------ ----- ----------
1 2 1 ,1
2 3 2 ,1,2
3 4 3 ,1,2,3
4 null 4 ,1,2,3,4
3 5 3 ,1,2,3
5 6 4 ,1,2,3,5
6 null 5 ,1,2,3,5,6
sys_connect_by_path関数のイメージは、下記となります。
connect by句では、sys_connect_by_path関数を使うことはできません。
-- ORA-30002: ここではSYS_CONNECT_BY_PATH関数を使用できません
select ID,NextID
from LevelSample
start with ID = 1
connect by prior NextID = ID
and instr(sys_connect_by_path(to_char(ID),','),'1,2,3') = 0;
where句でも、sys_connect_by_path関数を使うことはできません。
-- ORA-30002: ここではSYS_CONNECT_BY_PATH関数を使用できません
select ID,NextID
from LevelSample
where instr(sys_connect_by_path(to_char(ID),','),'1,2,3') = 0
start with ID = 1
connect by prior NextID = ID;
order siblings by
木の階層を崩さずにソート
siblingsというのは英語で、兄弟という意味です。 order by句で、order siblings byと指定すると、木の階層を崩さずにソートできます
create table siblingsSample(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 20,null from dual union all
select 21, 20 from dual union all
select 22, 21 from dual union all
select 23, 20 from dual;
-- order siblings byの使用例
select connect_by_root ID as treeID,
ID,OyaID,Level,
sys_connect_by_path(to_char(ID),',') as Path
from siblingsSample
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 23 20 2 ,20,23
20 21 20 2 ,20,21
20 22 21 3 ,20,21,22
1 1 null 1 ,1
1 3 1 2 ,1,3
1 5 3 3 ,1,3,5
1 4 3 3 ,1,3,4
1 2 1 2 ,1,2
SQLのイメージは下記となります。木ごとに区切る赤線をイメージしてます。
order siblings by ID descを指定してますので、下記の順序で行を返します。
start with句の条件を満たす根を選ぶ段階で、IDの大きいほうから選ばれる。
根からの深さ優先探索でも、子供が複数あったらIDの大きいほうから選ばれる。
そして深さ優先探索の行きがけ順で行を返す。