6-6 金額を超える組み合わせ(テーブル版)

SQLパズル

果物テーブル
ID  品物    金額
--  ------  ----
 1  りんご   200
 2  みかん   500
 3  もも     600
 4  かき     400
 5  いちご   800

果物テーブルから、1500円を超える組み合わせを出力する(TID列は、組み合わせ番号)

出力結果
TID  ID    品物  金額   合計金額
---  --  ------  ----  --------
  4   1  りんご   200      1700
  4   2  みかん   500      1700
  4   3  もも     600      1700
  4   4  かき     400      1700
  5   1  りんご   200      2500
  5   2  みかん   500      2500
  5   3  もも     600      2500
  5   4  かき     400      2500
  5   5  いちご   800      2500

データ作成スクリプト

create table 果物(
ID   number(1),
品物 varchar2(6),
金額 number(3));

insert into 果物 values(1,'りんご',200);
insert into 果物 values(2,'みかん',500);
insert into 果物 values(3,'もも'  ,600);
insert into 果物 values(4,'かき'  ,400);
insert into 果物 values(5,'いちご',800);
commit;

SQL

--■■■RowIDを使わない方法■■■
select TID,ID,品物,金額,合計金額
from (select a.TID,b.ID,b.品物,b.金額,
      sum(b.金額) over(partition by a.TID) as 合計金額
      from (select RowNum as TID,
            sys_connect_by_path(to_char(ID),',') as 組み合わせ
            from 果物
            connect by prior ID < ID) a,果物 b
      where instr(a.組み合わせ,to_char(b.ID)) > 0)
where 合計金額 > 1500;

--■■■RowIDを使う方法■■■
select TID,ID,品物,金額,合計金額
from (select a.TID,b.ID,b.品物,b.金額,
      sum(b.金額) over(partition by a.TID) as 合計金額
      from (select RowNum as TID,
            sys_connect_by_path(RowIDToChar(RowID),',') as 組み合わせ
            from 果物
            connect by prior ID < ID) a,果物 b
      where instr(a.組み合わせ,RowIDToChar(b.RowID)) > 0)
where 合計金額 > 1500;

解説

階層問い合わせで、2の5乗-1 = 31 通りの組み合わせを列挙してます。

---