再帰with句01 階層問い合わせのLevel擬似列を模倣

SQLパズル

IDTable
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
  from IDTable
start with OyaID is null
connect by prior ID = OyaID;

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

データ作成スクリプト

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

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

SQL

with rec(ID,OyaID,LV) as(
select ID,OyaID,1
  from IDTable
 where OyaID is null
union all
select b.ID,b.OyaID,a.LV+1
  from rec a,IDTable b
 where a.ID = b.OyaID)
select * from rec;

解説

深さ優先探索順で出力したいのであれば、下記のように
search句を使う必要があります。

with rec(ID,OyaID,LV) as(
select ID,OyaID,1
  from IDTable
 where OyaID is null
union all
select b.ID,b.OyaID,a.LV+1
  from rec a,IDTable b
 where a.ID = b.OyaID)
search depth first by ID set SortKey
select * from rec
order by SortKey;

ID  OyaID  LV  SortKey
--  -----  --  -------
 1   null   1        1
 2      1   2        2
 3      2   3        3
 4      3   4        4
 7      2   3        5
 5      1   2        6
 6      5   3        7
20   null   1        8
21     20   2        9
22     21   3       10