10-332 深さ優先探索順(Levelでソートキーを変更)で出力

SQLパズル

HierarchicalTable
ID  oyaID  Val
--  -----  ---
 1   null  900
 2      1  800
 3      1  333
 4      2  333
 5      2  333
 6      2  333
 7      4  333
 8      4  333
 9      5  333
10      5  333
11      6  333
12      6  333

start with oyaID is null
connect by prior ID = oyaID
で階層問い合わせを行ったとして、
order siblings by case when Level >= 3 then Val end,ID
のソートで深さ優先探索順に出力する。

出力結果
ID  oyaID  Val  path
--  -----  ---  ---------
 1   null  900  /1
 2      1  800  /1/2
 6      2  333  /1/2/6
11      6  333  /1/2/6/11
12      6  333  /1/2/6/12
 4      2  333  /1/2/4
 7      4  333  /1/2/4/7
 8      4  333  /1/2/4/8
 5      2  333  /1/2/5
 9      5  333  /1/2/5/9
10      5  333  /1/2/5/10
 3      1  333  /1/3

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table HierarchicalTable(ID,oyaID,Val) as
select  1,null,900 from dual union
select  2,   1,800 from dual union
select  3,   1,333 from dual union
select  4,   2,333 from dual union
select  5,   2,333 from dual union
select  6,   2,333 from dual union
select  7,   4,333 from dual union
select  8,   4,333 from dual union
select  9,   5,333 from dual union
select 10,   5,333 from dual union
select 11,   6,333 from dual union
select 12,   6,333 from dual;

SQL

col path for a10
col SK   for a20

--■■■再帰with句を使う方法(11gR2以降)■■■
with rec(ID,oyaID,Val,path,LV,SK1) as(
select ID,oyaID,Val,cast(ID as varchar2(20)),1,null
  from HierarchicalTable
 where oyaID is null
union all
select b.ID,b.oyaID,b.Val,a.path || '/' || to_char(b.ID),
a.LV+1,case when a.LV+1 >= 3 then b.Val end
  from rec a,HierarchicalTable b
 where a.ID = b.oyaID)
search depth first by SK1,ID set rn
select * from rec order by rn;

--■■■階層問い合わせを使う方法■■■
select ID,oyaID,Val,
sys_connect_by_path(to_char(ID),'/') as path,
sys_connect_by_path(case when Level >= 3
                         then to_char(rn,'fm0000')
                         else to_char(ID,'fm0000') end,'/') as SK
  from (select ID,oyaID,Val,
        Row_Number() over(order by Val) as rn
        from HierarchicalTable)
start with oyaID is null
connect by prior ID = oyaID
order by SK;

解説

階層問い合わせで深さ優先探索順で出力するには、
order siblings byを使ってソートすればいいのですが、Level擬似列を含めることができません。
そこで、sys_connect_by_path関数で経路を、固定長の文字列で大小関係を比較できるようにして連結させてます。

このデータでは、Valに重複値があるので、事前にインラインビューでRow_Number関数を使って、
一意なソートキーとしてます。

DB2 SQLパズル 再帰with句03 Oracleのorder siblings byを模倣

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
再帰with句を使う方法もあります。
再帰with句では、search句で指定可能なのは、with句での列エイリアスだけですので、
SK1という列エイリアスを別途用意してます。

*********************************************************************************************
subquery_factoring_clause ::=


*********************************************************************************************
search_clause::=

---