17,086
社区成员
发帖
与我相关
我的任务
分享
create table ab(id number
,"DATE" date
,shop varchar2(4 char)
,地点 varchar2(4 char)
,"总台数(T-T)" number
,剩余台数 number)
insert into ab values(1,to_date('2009-1-10','YYYY-MM-DD'),'东莞','黄埔',10,5);
insert into ab values(2,to_date('2009-1-10','YYYY-MM-DD'),'汕头','增城',10,10);
insert into ab values(3,to_date('2009-1-10','YYYY-MM-DD'),'广州','增城',40,0 );
insert into ab values(4,to_date('2009-1-10','YYYY-MM-DD'),'东莞','增城',20,10);
create table bc(id number
,DATE1 date
,shop varchar2(4 char)
,地点 varchar2(4 char)
,分配台数 number)
insert into bc values(2012,to_date('2009-01-09','YYYY-MM-DD'),'东莞','黄埔',5 ) ;
insert into bc values(2014,to_date('2009-01-09','YYYY-MM-DD'),'广州','增城',40 ) ;
insert into bc values(4567,to_date('2009-01-09','YYYY-MM-DD'),'东莞','增城',10 ) ;
select distinct '09-10'
,shop
,sum(decode(地点,'黄埔',台数,0)) over(partition by shop) 黄埔
,sum(decode(地点,'增城',台数,0)) over(partition by shop) 增城
from
(select shop,地点,剩余台数 台数 from ab
union all
select shop,地点,分配台数 from bc) order by shop
select min(a.id) id,b.date1 as date,a.shop,
sum(case when a.地点='黄埔' then a.剩余台数+nvl(b.分配台数,0) else 0 end) 黄埔总数,
sum(case when a.地点='增城' then a.剩余台数+nvl(b.分配台数,0) else 0 end) 增城总数
from 表1 a left join 表2 b on a.shop=b.shop
group by a.shop,b.date1