X sql解惑 41 预算问题答案

 

sql解惑 41 预算问题

create table items(
item_nbr number(8),
item_descr varchar2(20)
);

insert into items values(10,'item 10');
insert into items values(20,'item 20');
insert into items values(30,'item 30');
insert into items values(40,'item 40');
insert into items values(50,'item 50');


SQL> select * from items;

  ITEM_NBR ITEM_DESCR
---------- ----------------------------------------
        10 item 10
        20 item 20
        30 item 30
        40 item 40
        50 item 50
        
        
create table actuals(
item_nbr number(8),
actual_amt number(8,2),
check_nbr varchar2(20)
);

insert into actuals values(10,300.00,'1111');
insert into actuals values(20,325.00,'2222');
insert into actuals values(20,100.00,'3333');
insert into actuals values(30,525.00,'1111');


SQL> select * from actuals;

  ITEM_NBR ACTUAL_AMT CHECK_NBR
---------- ---------- ----------------------------------------
        10        300 1111
        20        325 2222
        20        100 3333
        30        525 1111
        
        
create table estimates(
item_nbr number(8),
estimated_amt number(8,2)
);


insert into estimates values(10,'300.00');
insert into estimates values(10,'50.00');
insert into estimates values(20,'325.00');
insert into estimates values(20,'110.00');
insert into estimates values(40,'25.00');


SQL> select * from estimates;

  ITEM_NBR ESTIMATED_AMT
---------- -------------
        10           300
        10            50
        20           325
        20           110
        40            25

要求获得以下结果：

  ITEM_NBR ITEM_DESCR                               ACTUAL_AMT ESTIMATED_AMT DEX
---------- ---------------------------------------- ---------- ------------- ----------------------------------------
        10 item 10                                         300           350 1111
        20 item 20                                         425           435 mixed
        30 item 30                                         525               1111
        40 item 40                                                        25

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


select case when a.item_nbr is null then e.item_nbr 
            when a.item_nbr is not null then a.item_nbr end as item_nbr,i.item_descr,a.actual_amt,e.estimated_amt,a.dex
from (select distinct item_nbr, sum(actual_amt)over(partition by item_nbr) actual_amt,
case when count(item_nbr)over(partition by item_nbr)>1 then 'mixed'
     when count(item_nbr)over(partition by item_nbr)=1 then check_nbr end as dex from actuals 
  )a
full outer join (select min(item_nbr) item_nbr,sum(estimated_amt) estimated_amt from estimates group by item_nbr) e on a.item_nbr = e.item_nbr
inner join items i on (e.item_nbr = i.item_nbr or a.item_nbr = i.item_nbr)
order by a.item_nbr,e.item_nbr

  ITEM_NBR ITEM_DESCR                               ACTUAL_AMT ESTIMATED_AMT DEX
---------- ---------------------------------------- ---------- ------------- ----------------------------------------
        10 item 10                                         300           350 1111
        20 item 20                                         425           435 mixed
        30 item 30                                         525               1111
        40 item 40                                                        25