Level擬似列,sys_connect_by_path関数

ノードのレベル,根からの経路

create table HierarchicalT(ID primary key,OyaID) as
select  1,null from dual union all
select  2,   1 from dual union all
select  3,   2 from dual union all
select  4,   3 from dual union all
select  5,   1 from dual union all
select  6,   5 from dual union all
select  7,   2 from dual union all
select 20,null from dual union all
select 21,  20 from dual union all
select 22,  21 from dual;

下記のLevel擬似列,sys_connect_by_path関数を使った階層問い合わせと同じ結果を、再帰with句で取得します。

-- 模倣対象の階層問い合わせ
select ID,OyaID,
Level,sys_connect_by_path(to_char(ID),',') as Path
  from HierarchicalT
start with OyaID is null
connect by prior ID = OyaID;
出力結果
ID  OyaID  Level  Path
--  -----  -----  ---------
 1   null      1  ,1
 2      1      2  ,1,2
 3      2      3  ,1,2,3
 4      3      4  ,1,2,3,4
 7      2      3  ,1,2,7
 5      1      2  ,1,5
 6      5      3  ,1,5,6
20   null      1  ,20
21     20      2  ,20,21
22     21      3  ,20,21,22
-- 再帰with句で模倣
with rec(ID,OyaID,LV,Path) as(
select ID,OyaID,1,',' || to_char(ID)
  from HierarchicalT
 where OyaID is null
union all
select b.ID,b.OyaID,a.LV+1,
a.Path || ',' || to_char(b.ID)
  from rec a,HierarchicalT b
 where a.ID = b.OyaID)
select * from rec;

Level擬似列を足し算で模倣し、sys_connect_by_path関数を文字列の連結で模倣してます。

▲ ページTOPに戻る

prior演算子,connect_by_root演算子

親ノードの値,根ノードの値

下記のprior演算子,connect_by_root演算子を使った階層問い合わせと同じ結果を、再帰with句で取得します。

-- 模倣対象の階層問い合わせ
select ID,OyaID,prior ID as preID,
connect_by_root ID as rootID,
Level,sys_connect_by_path(to_char(ID),',') as Path
  from HierarchicalT
start with OyaID is null
connect by prior ID = OyaID;

出力結果
ID  OyaID  preID  rootID  Level  Path
--  -----  -----  ------  -----  ---------
 1   null   null       1      1  ,1
 2      1      1       1      2  ,1,2
 3      2      2       1      3  ,1,2,3
 4      3      3       1      4  ,1,2,3,4
 7      2      2       1      3  ,1,2,7
 5      1      1       1      2  ,1,5
 6      5      5       1      3  ,1,5,6
20   null   null      20      1  ,20
21     20     20      20      2  ,20,21
22     21     21      20      3  ,20,21,22
-- 再帰with句で模倣
with rec(ID,OyaID,preID,rootID,LV,Path) as(
select ID,OyaID,to_number(null),ID,1,
',' || to_char(ID)
  from HierarchicalT
 where OyaID is null
union all
select b.ID,b.OyaID,a.ID,a.rootID,a.LV+1,
a.Path || ',' || to_char(b.ID)
  from rec a,HierarchicalT b
 where a.ID = b.OyaID)
select * from rec;

prior演算子を親ノードの値を使うことで模倣し、connect_by_root演算子を木の根のノードの値を使うことで模倣してます。

▲ ページTOPに戻る

order siblings by

深さ優先探索順で出力

create table siblingsT(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,   1 from dual union all
select  5,   3 from dual union all
select  6,   3 from dual union all
select  7,   4 from dual union all
select  8,   4 from dual union all
select  9,   6 from dual union all
select 10,   7 from dual union all
select 20,null from dual union all
select 21,  20 from dual union all
select 22,  20 from dual union all
select 23,  21 from dual union all
select 24,  21 from dual;

下記のorder siblings byを使った階層問い合わせと同じ結果を、再帰with句で取得します。

-- 模倣対象の階層問い合わせ
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
-- 再帰with句で模倣
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;

search句を使って、再帰withで探索を行った結果に対して、深さ優先探索順で連番を付与し、その連番をソートキーとして使用してます。

▲ ページTOPに戻る

Left Curve
図でイメージする
Oracle DatabaseのSQL全集
 Right Curve