2-1 金額を超える組み合わせ

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   int,
品物 varchar(6),
金額 int);

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

SQL

with rec(ID,IDリスト,合計金額) as(
select ID,cast(ID as VarChar(10)),金額
  from 果物
union all
select b.ID,
cast(a.IDリスト + ',' + cast(b.ID as VarChar(10)) as VarChar(10)),
a.合計金額 + b.金額
  from rec a,果物 b
 where a.ID < b.ID)
select a.TID,Row_Number() over(partition by a.TID order by b.ID) as ID,
b.品物,b.金額,a.合計金額
  from (select IDリスト,合計金額,
        Row_Number() over(order by IDリスト) as TID from rec) a
  Join 果物 b on CharIndex(cast(b.ID as VarChar),',' + a.IDリスト + ',') > 0
 where a.合計金額 > 1500
go

解説

再帰SQLで組み合わせを列挙してます。