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擬似列のイメージは下記となります。

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

▲ ページTOPに戻る

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関数のイメージは、下記となります。

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;

▲ ページTOPに戻る

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のイメージは下記となります。木ごとに区切る赤線をイメージしてます。

SQLのイメージ

order siblings by ID descを指定してますので、下記の順序で行を返します。

start with句の条件を満たす根を選ぶ段階で、IDの大きいほうから選ばれる。
根からの深さ優先探索でも、子供が複数あったらIDの大きいほうから選ばれる。
そして深さ優先探索の行きがけ順で行を返す。

▲ ページTOPに戻る

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