X sql解惑 34 咨询顾问收入问题

create table consultants(
emp_id number not null,
emp_name varchar2(10) not null
);

insert into consultants values(1,'larry');
insert into consultants values(2,'moe');
insert into consultants values(3,'curly');

select * from consultants;

    EMP_ID EMP_NAME
---------- --------------------
         1 larry
         2 moe
         3 curly


create table billings(
emp_id number not null,
bill_date date not null,
bill_rate number(5,2)
);

insert into billings values(1,date'1990-01-01',25.00);
insert into billings values(2,date'1989-01-01',15.00);
insert into billings values(3,date'1989-01-01',20.00);
insert into billings values(1,date'1991-01-01',30.00);

select * from billings;

    EMP_ID BILL_DATE   BILL_RATE
---------- ---------- ----------
         1 1990-01-01         25
         2 1989-01-01         15
         3 1989-01-01         20
         1 1991-01-01         30


create table hoursworked(
job_id number not null,
emp_id number not null,
work_date date not null,
bill_hrs number(5,2)
);
insert into hoursworked values(4,1,date'1990-07-01',3);
insert into hoursworked values(4,1,date'1990-08-01',5);
insert into hoursworked values(4,2,date'1990-07-01',2);
insert into hoursworked values(4,1,date'1991-07-01',4);

select * from hoursworked

    JOB_ID     EMP_ID WORK_DATE    BILL_HRS
---------- ---------- ---------- ----------
         4          1 1990-07-01          3
         4          1 1990-08-01          5
         4          2 1990-07-01          2
         4          1 1991-07-01          4

====================================================================================================================



需要的 答案是 

name  totalcharges
larry  320
moe    30

 

===================================================================================================================================
===================================================================================================================================


答案：


select abc.emp_id, sum(bill_rate * bill_hrs) totalcharges
  from (select b.emp_id,
               b.bill_date,
               h.work_date,
               max(b.bill_date) over(partition by h.emp_id, h.work_date) bill_date_max,
               c.emp_name,
               b.bill_rate,
               h.bill_hrs,
               b.bill_rate * h.bill_hrs
          from billings b
         inner join hoursworked h
            on b.emp_id = h.emp_id
         inner join consultants c
            on h.emp_id = c.emp_id
         where b.bill_date < h.work_date
         order by b.bill_date, h.work_date) abc
 where bill_date_max = bill_date
 group by emp_id ;

    EMP_ID TOTALCHARGES
---------- ------------
         1          320
         2           30