10-149 Oracle9iでconnect_by_isleafを模倣

SQLパズル

CloneIsLeafテーブル
ID  PrevID
--  ------
 1    null
 2       1
 3       1
 4       3

Oracle9iで、階層問い合わせを行い、
Oracle10gのconnect_by_isleafを模倣する

出力結果
ID  PrevID  isLeaf
--  ------  ------
 1    null       0
 2       1       1
 3       1       0
 4       3       1

データ作成スクリプト

create table CloneIsLeaf as
select 1 as ID,null as PrevID from dual
union select 2,1 from dual
union select 3,1 from dual
union select 4,3 from dual;

SQL

--■■■case式を使う方法■■■
select ID,PrevID,
case when exists(select 1 from CloneIsLeaf b
                  where a.ID = b.PrevID) then 0 else 1 end as isLeaf
  from CloneIsLeaf a
Start With ID = 1
connect by prior ID = PrevID;

--■■■order siblings byでソートしない方法■■■
select ID,PrevID,
case when Level < Lead(Level) over(order by RowNum)
     then 0 else 1 end as isLeaf
  from CloneIsLeaf
Start With ID = 1
connect by prior ID = PrevID;

--■■■order siblings byでソートする方法1■■■
select ID,PrevID,
case when LV < Lead(LV) over(order by RowNum)
     then 0 else 1 end as isLeaf
from (select ID,PrevID,Level as LV
        from CloneIsLeaf
      Start With ID = 1
      connect by prior ID = PrevID
      order siblings by ID);

--■■■order siblings byでソートする方法2■■■
select ID,PrevID,
case when LV < Lead(LV) over(order by Row_Num)
     then 0 else 1 end as isLeaf
from (select ID,PrevID,LV,RowNum as Row_Num
        from (select ID,PrevID,Level as LV
                from CloneIsLeaf
              Start With ID = 1
              connect by prior ID = PrevID
              order siblings by ID));

解説

階層問い合わせが、
深さ優先探索の、探索順に
行を返すことを使ってます。

order siblings byでソートする方法2
が無難だと思います。

一応、case式で模倣することもできます。

8-25 Oracle9iで、connect_by_rootを模倣
8-43 Oracle9iでnocycleを模倣

---

マニュアル(英語)
マニュアル

---