9-32 SQL99構文での結合順序

SQLパズル

Consultantsテーブル
empID  name
-----  -----
    1  Larry
    2  Moe
    3  Curly
    4  Wendy
    5  Tom

Billingsテーブル
empID  effectDate  billRate
-----  ----------  --------
    1  1990/01/01      2500
    2  1989/01/01      1500
    3  1989/01/01      2000
    1  1991/01/01      3000
    4  2006/10/01      6000
    4  2006/11/01      8000
    4  2006/12/01      9000
    5  2006/12/01      1000

HoursWorkedテーブル
empID  workDate    billHours
-----  ----------  ---------
    1  1990/07/01          3
    1  1990/08/01          5
    1  1991/07/01          4
    2  1990/07/01          2
    4  2006/10/20          4
    4  2006/11/20          8
    4  2006/12/20         12

Consultantsテーブル(コンサルタントテーブル)
Billingsテーブル(自給テーブル)
HoursWorkedテーブル(勤務履歴テーブル)

を参照して、
コンサルタントごとの給料を求める

出力結果
name   totalCharges
-----  ------------
Wendy        196000
Larry         32000
Moe            3000
Curly             0

SQLパズル(日本語版)のパズル33 [コンサルタントへの支払い] を参考にさせていただきました
SQLパズル 第2版のパズル34 [コンサルタントの請求書] を参考にさせていただきました

データ作成スクリプト

create table Consultants(
empID varchar2(1) primary key,
name  varchar2(5));

insert into Consultants values(1,'Larry');
insert into Consultants values(2,'Moe');
insert into Consultants values(3,'Curly');
insert into Consultants values(4,'Wendy');
insert into Consultants values(5,'Tom');

create table Billings(
empID      varchar2(1),
effectDate date,
billRate   number(4),
primary key(empID,effectDate));

insert into Billings values(1,to_date('1990/01/01','yyyy/mm/dd'),2500);
insert into Billings values(2,to_date('1989/01/01','yyyy/mm/dd'),1500);
insert into Billings values(3,to_date('1989/01/01','yyyy/mm/dd'),2000);
insert into Billings values(1,to_date('1991/01/01','yyyy/mm/dd'),3000);
insert into Billings values(4,to_date('2006/10/01','yyyy/mm/dd'),6000);
insert into Billings values(4,to_date('2006/11/01','yyyy/mm/dd'),8000);
insert into Billings values(4,to_date('2006/12/01','yyyy/mm/dd'),9000);
insert into Billings values(5,to_date('2006/12/01','yyyy/mm/dd'),1000);

create table HoursWorked(
empID     varchar2(1),
workDate  date,
billHours number(2),
primary key(empID,workDate));

insert into HoursWorked values(1,to_date('1990/07/01','yyyy/mm/dd'), 3);
insert into HoursWorked values(1,to_date('1990/08/01','yyyy/mm/dd'), 5);
insert into HoursWorked values(1,to_date('1991/07/01','yyyy/mm/dd'), 4);
insert into HoursWorked values(2,to_date('1990/07/01','yyyy/mm/dd'), 2);
insert into HoursWorked values(4,to_date('2006/10/20','yyyy/mm/dd'), 4);
insert into HoursWorked values(4,to_date('2006/11/20','yyyy/mm/dd'), 8);
insert into HoursWorked values(4,to_date('2006/12/20','yyyy/mm/dd'),12);
commit;

SQL

--■■■分析関数を使う方法1■■■
select name,nvl(sum(billRate*billHours),0) as totalCharges
from (select a.name,b.billRate,c.billHours,b.effectDate,
      max(b.effectDate) over(partition by b.empID,c.workDate) as maxeffectDate
        from Consultants a join Billings b
          on a.empID = b.empID
        Left Join HoursWorked c
          on c.empID = a.empID
         and c.workDate >= b.effectDate)
where effectDate = maxeffectDate
group by name
order by totalCharges desc,name;

--■■■分析関数を使う方法2■■■
select name,nvl(sum(billRate*billHours),0) as totalCharges
from (select a.name,b.billRate,c.billHours,b.effectDate,
      max(b.effectDate) over(partition by a.empID,c.workDate) as maxeffectDate
        from Consultants a Left join HoursWorked c
          on a.empID = c.empID
        Left join Billings b
          on a.empID = b.empID
         and b.effectDate <= c.workDate)
where effectDate = maxeffectDate or maxeffectDate is null
group by name
order by totalCharges desc,name;

--■■■分析関数を使わない方法1■■■
select a.name,
nvl(sum(case when b.effectDate = (select max(d.effectDate)
                                    from Billings d
                                   where d.empID = b.empID
                                     and c.workDate >= d.effectDate)
             then b.billRate*c.billHours end),0) as totalCharges
  from Consultants a join Billings b
    on a.empID = b.empID
  Left Join HoursWorked c
    on c.empID = a.empID
   and c.workDate >= b.effectDate
group by name
order by totalCharges desc,name;

--■■■分析関数を使わない方法2■■■
select a.name,
nvl(sum(case when b.effectDate = (select max(d.effectDate)
                                    from Billings d
                                   where d.empID = b.empID
                                     and c.workDate >= d.effectDate)
             then b.billRate*c.billHours end),0) as totalCharges
  from Consultants a Left Join HoursWorked c
    on a.empID = c.empID
  Left Join Billings b
    on b.empID = a.empID
   and b.effectDate <= c.workDate
group by name
order by totalCharges desc,name;

解説

三つ以上のテーブルを結合するクエリでは、
結合順序によって、クエリを簡略化できることがあります

---