9-47 期間内の合計

SQLパズル

Promotionsテーブル
Promo                    startDate   endDate
-----------------------  ----------  ----------
Christmas Week           1995/12/18  1995/12/25
Feast of St.Fred         1995/02/01  1995/02/07
National Pickle Pageant  1995/11/01  1995/11/07

Salesテーブル
clerk  saledate    amount
-----  ----------  ------
Curly  1995/02/03     260
Curly  1995/02/04     100
Curly  1995/02/05     400
Larry  1995/11/01     325
Larry  1995/11/02     150
Larry  1995/12/23     260
Larry  1995/12/24      25
Moe    1995/12/20     200
Moe    1996/01/04     100

Promoごと、clerkごとに
amountの合計を
以下の形式で出力する

出力結果
Promo                    clerk  amount
-----------------------  -----  ------
Christmas Week           Curly       0
Christmas Week           Larry     285
Christmas Week           Moe       200
Feast of St.Fred         Curly     760
Feast of St.Fred         Larry       0
Feast of St.Fred         Moe         0
National Pickle Pageant  Curly       0
National Pickle Pageant  Larry     475
National Pickle Pageant  Moe         0

9-38 期間内の合計の最大値のアレンジ問題です

データ作成スクリプト

create table Promotions(
Promo     varchar2(23) not null,
startDate date not null,
endDate   date not null);

insert into Promotions values('Feast of St.Fred',to_date('1995/02/01','YYYY/MM/DD'),
                                                 to_date('1995/02/07','YYYY/MM/DD'));
insert into Promotions values('National Pickle Pageant',to_date('1995/11/01','YYYY/MM/DD'),
                                                        to_date('1995/11/07','YYYY/MM/DD'));
insert into Promotions values('Christmas Week',to_date('1995/12/18','YYYY/MM/DD'),
                                               to_date('1995/12/25','YYYY/MM/DD'));

create table Sales(
clerk    varchar2(5) not null,
saledate date        not null,
amount   number(3)   not null);

insert into Sales values('Curly',to_date('1995/02/03','YYYY/MM/DD'),260);
insert into Sales values('Curly',to_date('1995/02/04','YYYY/MM/DD'),100);
insert into Sales values('Curly',to_date('1995/02/05','YYYY/MM/DD'),400);

insert into Sales values('Larry',to_date('1995/11/01','YYYY/MM/DD'),325);
insert into Sales values('Larry',to_date('1995/11/02','YYYY/MM/DD'),150);
insert into Sales values('Larry',to_date('1995/12/23','YYYY/MM/DD'),260);
insert into Sales values('Larry',to_date('1995/12/24','YYYY/MM/DD'), 25);

insert into Sales values('Moe'  ,to_date('1995/12/20','YYYY/MM/DD'),200);
insert into Sales values('Moe'  ,to_date('1996/01/04','YYYY/MM/DD'),100);
commit;

SQL

select a.Promo,b.clerk,
sum(case when(b.saledate between a.startDate and a.endDate)
         then b.amount else 0 end) as amount
  from Promotions a,Sales b
group by a.Promo,b.clerk
order by a.Promo,b.clerk;

解説

sum関数とcase式を組み合わせてます

---