再帰with句04 階層問い合わせのconnect_by_IsLeafを模倣

SQLパズル

IsLeafT
ID  OyaID
--  -----
 1   null
 2      1
 3      2
 4      3
 5      1
 6      5
 7      2
20   null
21     20
22     21

階層問い合わせを使った下記のクエリと同じ結果を取得する。

select ID,OyaID,Level,sys_connect_by_path(to_char(ID),',') as Path,
connect_by_IsLeaf as IsLeaf
  from IsLeafT
start with OyaID is null
connect by prior ID = OyaID;

出力結果
ID  OyaID  Level  Path       IsLeaf
--  -----  -----  ---------  ------
 1   null      1  ,1         0
 2      1      2  ,1,2       0
 3      2      3  ,1,2,3     0
 4      3      4  ,1,2,3,4   1
 7      2      3  ,1,2,7     1
 5      1      2  ,1,5       0
 6      5      3  ,1,5,6     1
20   null      1  ,20        0
21     20      2  ,20,21     0
22     21      3  ,20,21,22  1

データ作成スクリプト

create table IsLeafT(
ID    number primary key,
OyaID number);

insert into IsLeafT values( 1,null);
insert into IsLeafT values( 2,   1);
insert into IsLeafT values( 3,   2);
insert into IsLeafT values( 4,   3);
insert into IsLeafT values( 5,   1);
insert into IsLeafT values( 6,   5);
insert into IsLeafT values( 7,   2);
insert into IsLeafT values(20,null);
insert into IsLeafT values(21,  20);
insert into IsLeafT values(22,  21);
commit;

SQL

col path for a10

--■■■exists述語を使う方法■■■
with rec(ID,OyaID,LV,Path) as(
select ID,OyaID,1,',' || to_char(ID)
  from IsLeafT
 where OyaID is null
union all
select b.ID,b.OyaID,a.LV+1,a.Path || ',' || to_char(b.ID)
  from rec a,IsLeafT b
 where a.ID = b.OyaID)
search depth first by ID set SortKey
select ID,OyaID,LV,Path,
case when exists(select 1 from rec b
                  where a.ID = b.OyaID)
     then 0 else 1 end as IsLeaf
  from rec a;

--■■■深さ優先探索の行きがけ順でレベルを比較する方法■■■
with rec(ID,OyaID,LV,Path) as(
select ID,OyaID,1,',' || to_char(ID)
  from IsLeafT
 where OyaID is null
union all
select b.ID,b.OyaID,a.LV+1,a.Path || ',' || to_char(b.ID)
  from rec a,IsLeafT b
 where a.ID = b.OyaID)
search depth first by ID set SortKey
select ID,OyaID,LV,Path,
case when Lead(LV) over(order by SortKey) > LV
     then 0 else 1 end as IsLeaf
  from rec
order by SortKey;

解説

case式でexists述語を使う方法と
Lead関数を使う方法を
状況に応じて使い分けるといいでしょう。

OracleSQLパズル 10-149 Oracle9iでconnect_by_isleafを模倣