10-346 再帰with句の再帰項でLeft Join

SQLパズル

IDTable
ID    OyaID
----  -----
AAAA  null
BBBB  AAAA
CCCC  BBBB
XXXX  null
YYYY  XXXX
1111  null
2222  1111
3333  2222
4444  3333

OyaIDがnullの行を根として、木構造を出力する。
ただし、全ての葉の高さが4となるように、ノードを補完する。

出力結果
LV  tree
--  ----------------
 1  AAAA
 2    --BBBB
 3        --CCCC
 4            --CCCC
 1  XXXX
 2    --YYYY
 3        --YYYY
 4            --YYYY
 1  1111
 2    --2222
 3        --3333
 4            --4444

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

データ作成スクリプト

create table IDTable(ID primary key,OyaID) as
select 'AAAA',null   from dual union all
select 'BBBB','AAAA' from dual union all
select 'CCCC','BBBB' from dual union all
select 'XXXX',null   from dual union all
select 'YYYY','XXXX' from dual union all
select '1111',null   from dual union all
select '2222','1111' from dual union all
select '3333','2222' from dual union all
select '4444','3333' from dual;

SQL

col tree for a20

with rec(ID,LV) as(
select ID,1
  from IDTable
 where OyaID is null
union all
select nvl(b.ID,a.ID),a.LV+1
  from rec a Left Join IDTable b
    on a.ID = b.OyaID
 where a.LV+1 <= 4)
search depth first by ID set rn
CYCLE LV SET IsLoop TO 'Y' DEFAULT 'N'
select LV,
case when LV >= 2
     then LPad(' ',LV*4-6) || '--'
end || ID as tree
from rec order by rn;

解説

再帰with句の再帰項でLeft Joinを使って、ノードを補完しつつ
where句で枝切りを行うことができます。
PostgreSQLの再帰SQLの使用例 「8. 枝切り(外部結合後のwhere句)」

CYCLE LV SET IsLoop TO 'Y' DEFAULT 'N'
を記述しているのは、
ORA-32044: (再帰的WITH問合せの実行中にサイクルが検出されました) に対する対策でして、
パーサを騙すためのものです。(親子ノードにおいてレベル列が等しいということはありえないので)


ちなみに下記のSQLは、
scott.empテーブルを対象として、補完列を*でくくったものです。

with rec(empno,ename,LV) as(
select empno,ename,1
  from scott.emp
 where mgr is null
union all
select b.empno,
nvl(b.ename,'*' || a.ename || '*'),a.LV+1
  from rec a Left Join scott.emp b
    on a.empno = b.mgr
 where LV+1 <= 4)
search depth first by empno set rn
select LPad(' ',(LV-1)*2) || ename as DISPLAY_ID
 from rec order by rn;

DISPLAY_ID
--------------
KING
  JONES
    SCOTT
      ADAMS
    FORD
      SMITH
  BLAKE
    ALLEN
      *ALLEN*
    WARD
      *WARD*
    MARTIN
      *MARTIN*
    TURNER
      *TURNER*
    JAMES
      *JAMES*
  CLARK
    MILLER
      *MILLER*