9-15 階層問い合わせの根と葉も出力

SQLパズル

IDListテーブル
ID  NextID
--  ------
 1       2
 2       3
 3    null
10    null
20      21
21      30
22      30
30    null

各レコードの、根と葉のIDも出力する

ID=30のレコードのように、
根となるレコードが複数存在すれば、
複数レコード出力する

出力結果
ID  NextID  RootID  LeafID
--  ------  ------  ------
 1       2       1       3
 2       3       1       3
 3    null       1       3
10    null      10      10
20      21      20      30
21      30      20      30
22      30      22      30
30    null      20      30
30    null      22      30

SQLパズル(日本語版)のパズル6 [書類の連鎖] を参考にさせていただきました
SQLパズル 第2版のパズル7 [ファイルのバージョン管理] を参考にさせていただきました

データ作成スクリプト

create table IDList(
ID     number(2),
NextID number(2));

insert into IDList values( 1,   2);
insert into IDList values( 2,   3);
insert into IDList values( 3,null);
insert into IDList values(10,null);
insert into IDList values(20,  21);
insert into IDList values(21,  30);
insert into IDList values(22,  30);
insert into IDList values(30,null);
commit;

SQL

--■■■connect_by_rootを使う方法(10g以降)■■■
select ID,NextID,RootID,
Last_Value(ID) over(partition by RootID order by LV
Rows between Unbounded Preceding and Unbounded Following) as LeafID
from (select ID,NextID,
      connect_by_root ID as RootID,
      Level as LV
        from IDList a
      Start With not exists(select 1 from IDList b
                             where b.NextID = a.ID)
      connect by prior NextID = ID);

--■■■sys_connect_by_pathから根を取得する方法■■■
select ID,NextID,RootID,
Last_Value(ID) over(partition by RootID order by LV
Rows between Unbounded Preceding and Unbounded Following) as LeafID
from (select ID,NextID,
      to_number(
      substr(sys_connect_by_path(to_char(ID),','),2,
             decode(instr(substr(sys_connect_by_path(to_char(ID),','),2),','),
                    0,Length(substr(sys_connect_by_path(to_char(ID),','),2)),
                    instr(substr(sys_connect_by_path(to_char(ID),','),2),',')-1))) as RootID,
      Level as LV
        from IDList a
      Start With not exists(select 1 from IDList b
                             where b.NextID = a.ID)
      connect by prior NextID = ID);

解説

Last_Value関数で、葉のIDを求めてます。

4-9 階層問い合わせの根を取得
7-27 階層問い合わせの根と葉を取得

---