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

SQLパズル

以下のテーブルから始祖と末裔を出力する。

親         嫡男
--------   --------
織田信秀   織田信長
織田信長   織田信忠
織田信忠   織田秀信
真田昌幸   真田幸村
真田幸村   真田大助
豊臣秀吉   豊臣秀頼

出力結果
始祖       末裔
--------   --------
真田昌幸   真田大助
豊臣秀吉   豊臣秀頼
織田信秀   織田秀信

データ作成スクリプト

create table 系譜(
親   char(8),
嫡男 char(8));

insert into 系譜 values('織田信秀','織田信長');
insert into 系譜 values('織田信長','織田信忠');
insert into 系譜 values('織田信忠','織田秀信');
insert into 系譜 values('真田昌幸','真田幸村');
insert into 系譜 values('真田幸村','真田大助');
insert into 系譜 values('豊臣秀吉','豊臣秀頼');
commit;

SQL

col 始祖 for a10
col 末裔 for a10

--■■■Connect_by_RootとConnect_by_IsLeafを使う方法(10g以降)■■■
select Connect_by_Root 親 as 始祖,
嫡男 as 末裔
from 系譜 a
where Connect_by_IsLeaf = 1
start with not exists(select 1 from 系譜 b where a.親=b.嫡男)
connect by prior 嫡男=親;

--■■■分析関数とLevelを組み合わせて葉か判断する方法■■■
select 始祖,末裔
from (select LV,Max(LV) over(partition by 始祖) as MaxLV,始祖,末裔
      from (select Level as LV,
            substr(sys_connect_by_path(親,','),2,
            decode(instr(substr(sys_connect_by_path(親,','),2),','),
                   0,Length(substr(sys_connect_by_path(親,','),2)),
                   instr(substr(sys_connect_by_path(親,','),2),',')-1)) as 始祖,
            嫡男 as 末裔
            from 系譜 a
            start with not exists(select 1 from 系譜 b where a.親=b.嫡男)
            connect by prior 嫡男=親)
     )
where LV=MaxLV;

解説

Oracle10gからは、
Connect_by_IsLeafを使って葉か判断することができ、
Connect_by_Rootで根の情報を取得できます。

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

---