10-333 経路上の各レベルの値を表示

SQLパズル

treeLevelT
oya   ko
----  ---
null  333
 333  222
 222  111
null  555
 555  444
null  777

start with oya is null
connect by prior ko=oya
で階層問い合わせを行い、
経路上の各レベルの値を表示する。(レベルの上限は3)

出力結果
Node1  Node2  Node3
-----  -----  -----
  333   null   null
  333    222   null
  333    222    111
  555   null   null
  555    444   null
  777   null   null

データ作成スクリプト

create table treeLevelT(oya,ko) as
select null,333 from dual union all
select  333,222 from dual union all
select  222,111 from dual union all
select null,555 from dual union all
select  555,444 from dual union all
select null,777 from dual;

SQL

--■■■分析関数を使う方法■■■
select
max(Node1) over(partition by root order by LV) as Node1,
max(Node2) over(partition by root order by LV) as Node2,
max(Node3) over(partition by root order by LV) as Node3
from (select connect_by_root ko as root,Level as LV,
      decode(Level,1,ko) as Node1,
      decode(Level,2,ko) as Node2,
      decode(Level,3,ko) as Node3
        from treeLevelT
      start with oya is null
      connect by prior ko=oya)
order by Node1,LV desc;

--■■■正規表現を使う方法(10g以降)■■■
col strNode1 for a10
col strNode2 for a10
col strNode3 for a10

select
RegExp_Substr(path,'[^,]+',1,1) as strNode1,
RegExp_Substr(path,'[^,]+',1,2) as strNode2,
RegExp_Substr(path,'[^,]+',1,3) as strNode3
from (select sys_connect_by_path(to_char(ko),',') as path
        from treeLevelT
      start with oya is null
      connect by prior ko=oya)
order by strNode1,strNode2,strNode3;

--■■■再帰with句を使う方法(11gR2以降)■■■
with rec(oya,ko,LV,Node1,Node2,Node3) as(
select oya,ko,1,ko,to_number(null),to_number(null)
  from treeLevelT
 where oya is null
union all
select b.oya,b.ko,a.LV+1,a.Node1,
case a.LV when 1 then b.ko else a.Node2 end,
case a.LV when 2 then b.ko else a.Node3 end
  from rec a,treeLevelT b
 where a.ko = b.oya)
select Node1,Node2,Node3 from rec order by Node1,LV desc;

解説

階層問い合わせではできませんが、
再帰with句では、select句での再帰的なデータの加工が可能です。

---