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

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

 

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

标签：null   consul   class   arch   http   job   not   font   咨询顾问