4-13 経路を取得

SQLパズル

航空便表テーブル
出発地        目的地             費用
------------  ------------      ----
パリ          ニューヨーク         6
パリ          デンバー             9
パリ          ダラス               8
ニューヨーク  シカゴ               2
シカゴ        サンフランシスコ     4
デンバー      サンフランシスコ     4
ダラス        ニューヨーク         3
ダラス        サンフランシスコ     6

パリからサンフランシスコまでの経路リストと、合計費用を取得する

出力結果
経路                                              合計費用  乗継回数
-----------------------------------------------  --------  --------
パリ,ニューヨーク,シカゴ,サンフランシスコ                12         2
パリ,デンバー,サンフランシスコ                           13         1
パリ,ダラス,サンフランシスコ                             14         1
パリ,ダラス,ニューヨーク,シカゴ,サンフランシスコ          17         3

データ作成スクリプト

create table 航空便表(
出発地 varchar2(12),
目的地 varchar2(16),
費用   number(1));

insert into 航空便表 values('パリ'        ,'ニューヨーク'    ,6);
insert into 航空便表 values('パリ'        ,'デンバー'        ,9);
insert into 航空便表 values('パリ'        ,'ダラス'          ,8);
insert into 航空便表 values('ニューヨーク','シカゴ'          ,2);
insert into 航空便表 values('シカゴ'      ,'サンフランシスコ',4);
insert into 航空便表 values('デンバー'    ,'サンフランシスコ',4);
insert into 航空便表 values('ダラス'      ,'ニューヨーク'    ,3);
insert into 航空便表 values('ダラス'      ,'サンフランシスコ',6);
commit;

SQL

col 経路 for a50

--■■■経路で部分一致を行う方法■■■
select 経路,
(select nvl(sum(b.費用),0) from 航空便表 b
 where instr(a.経路,b.出発地 || ',' || b.目的地) > 0) as 合計費用,
乗継回数
from
(select 'パリ' || sys_connect_by_path(目的地,',') as 経路,
 Level-1 as 乗継回数
  from 航空便表
 where Connect_by_IsLeaf = 1
start with 出発地 = 'パリ'
connect by prior 目的地 = 出発地) a
order by 合計費用;

--■■■RowIDリストで部分一致を行う方法■■■
select 経路,
(select nvl(sum(b.費用),0) from 航空便表 b
 where instr(a.RowIDList,RowIDToChar(b.RowID)) > 0) as 合計費用,
乗継回数
from
(select 'パリ' || sys_connect_by_path(目的地,',') as 経路,
 sys_connect_by_path(RowIDToChar(RowID),',') as RowIDList,
 Level-1 as 乗継回数
  from 航空便表
 where Connect_by_IsLeaf = 1
start with 出発地 = 'パリ'
connect by prior 目的地 = 出発地) a
order by 合計費用;

解説

sys_connect_by_path関数で経路を求めて、
sum関数を組み合わせて、合計費用を取得してます

---