6-4 金額を超える組み合わせ

SQLパズル

りんご 200円 (IDは1)
みかん 500円 (IDは2)
もも   600円 (IDは3)
かき   400円 (IDは4)
いちご 800円 (IDは5)
として、1500円を超える組み合わせを出力する(TID列は、組み合わせ番号)

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

SQL

with WorkView as (
select Row_Number() over (order by a,b,c,d,e) as TID,a,b,c,d,e
from (select 200 as a from dual union all select 0 from dual),
     (select 500 as b from dual union all select 0 from dual),
     (select 600 as c from dual union all select 0 from dual),
     (select 400 as d from dual union all select 0 from dual),
     (select 800 as e from dual union all select 0 from dual))
select TID,1 as ID,'りんご' as 品物,a as 金額,a+b+c+d+e as 合計金額 from WorkView
where a+b+c+d+e>1500 and a!=0
union all select TID,2,'みかん',b,a+b+c+d+e from WorkView where a+b+c+d+e>1500 and b!=0
union all select TID,3,'もも'  ,c,a+b+c+d+e from WorkView where a+b+c+d+e>1500 and c!=0
union all select TID,4,'かき'  ,d,a+b+c+d+e from WorkView where a+b+c+d+e>1500 and d!=0
union all select TID,5,'いちご',e,a+b+c+d+e from WorkView where a+b+c+d+e>1500 and e!=0
order by 1,2;

解説

with句の中で、
集合の直積演算を使って、全ての組み合わせを取得してます。

全ての組み合わせは、2の5乗で32通りなので、
クロスジョインを使って、32行のレコードを作成し、
(集合の直積の要素数の公式の、
ｎ(Ａ×Ｂ×Ｃ×Ｄ×Ｅ)＝ｎ(Ａ)×ｎ(Ｂ)×ｎ(Ｃ)×ｎ(Ｄ)×ｎ(Ｅ)
を使用)

後は、合計が1500円より大きいレコードを、行列変換を行って出力してます。

---

マニュアル(WITH句を使用した計算)
SQL 問い合わせ - スカラー副問い合わせ、cursor、row_number、over、with - SAK Streets

---