10-201 多対1の外部結合を変形

SQLパズル

MACHINEテーブル
COUNTRY  CUSTOMER  YMD      COUNT  S_N
-------  --------  -------  -----  ----
CANADA   DELL      2007/04      1  S001
CANADA   DELL      2007/05      2  S001
CANADA   DELL      2007/06      4  S001
CANADA   DELL      2007/04      8  S002
CANADA   DELL      2007/05     16  S002
CANADA   DELL      2007/06     32  S002
CHINA    DELL      2007/04     64  S001
CHINA    DELL      2007/05    128  S001
CHINA    DELL      2007/06    256  S001

URIAGEテーブル
COUNTRY  CUSTOMER  MONEY  YMD      S_N
-------  --------  -----  -------  ----
CANADA   DELL      10000  2007/04  S001
CANADA   DELL      20000  2007/05  S001
CANADA   DELL      40000  2007/06  S001

MACHINEテーブルの、COUNTRY,CUSTOMERごとの
最大のYMDと、
YMDが最大の行のCOUNTの合計と、
COUNTRY,CUSTOMER,YMD,S_Nが一致するURIAGEテーブルのMONEYの合計(なければ0)
を取得する。

出力結果
COUNTRY  CUSTOMER  MaxYMD   SumMONEY  COUNT
-------  --------  -------  --------  -----
CANADA   DELL      2007/06     70000     36
CHINA    DELL      2007/06         0    256

データ作成スクリプト

create table MACHINE(
COUNTRY  varchar2(6),
CUSTOMER varchar2(4),
YMD      char(7),
COUNT    number(3),
S_N      varchar2(4));

create table URIAGE(
COUNTRY  varchar2(6),
CUSTOMER varchar2(4),
MONEY    number(5),
YMD      char(7),
S_N      varchar2(4));

insert into MACHINE values('CANADA','DELL','2007/04',  1,'S001');
insert into MACHINE values('CANADA','DELL','2007/05',  2,'S001');
insert into MACHINE values('CANADA','DELL','2007/06',  4,'S001');
insert into MACHINE values('CANADA','DELL','2007/04',  8,'S002');
insert into MACHINE values('CANADA','DELL','2007/05', 16,'S002');
insert into MACHINE values('CANADA','DELL','2007/06', 32,'S002');
insert into MACHINE values('CHINA' ,'DELL','2007/04', 64,'S001');
insert into MACHINE values('CHINA' ,'DELL','2007/05',128,'S001');
insert into MACHINE values('CHINA' ,'DELL','2007/06',256,'S001');
insert into URIAGE  values('CANADA','DELL',10000,'2007/04','S001');
insert into URIAGE  values('CANADA','DELL',20000,'2007/05','S001');
insert into URIAGE  values('CANADA','DELL',40000,'2007/06','S001');
commit;

SQL

--■■■外部結合を使う方法■■■
select b.country,b.customer,max(b.YMD) as MaxYMD,
sum(nvl(a.money,0)) as SumMONEY,
sum("COUNT") Keep (Dense_Rank Last order by b.YMD) as "COUNT"
  from uriage a,machine b
 where a.country(+)=b.country
   and a.ymd(+)=b.ymd
   and a.customer(+)=b.customer
   and a.s_n(+)=b.s_n
group by b.country,b.customer
order by b.country,b.customer;

--■■■select句で相関サブクエリを使う方法■■■
select COUNTRY,CUSTOMER,max(YMD) as MaxYMD,
sum(nvl((select b.MONEY
           from URIAGE b
          where b.COUNTRY  = a.COUNTRY
            and b.CUSTOMER = a.CUSTOMER
            and b.YMD = a.YMD
            and b.S_N = a.S_N),0)) as SumMONEY,
sum("COUNT") Keep (Dense_Rank Last order by YMD) as "COUNT"
  from MACHINE a
group by COUNTRY,CUSTOMER
order by COUNTRY,CUSTOMER;

解説

外部結合を使う方法を変形して、
select句で相関サブクエリを使ったほうが
分かりやすいかもしれないですね。

---