该楼层疑似违规已被系统折叠 隐藏此楼查看此楼
--建立测试表
create table test_a (t_date date,name varchar2(22),A number,B number,C number,D number);
create table test_b(t_Date date,name varchar2(22),t_type varchar2(22),t_number number) ;
--插入测试数据
insert into test_a values(to_Date('2015-09-10','yyyy-mm-dd'),'小明',1,3,1,3);
insert into test_a values(to_Date('2015-09-10','yyyy-mm-dd'),'小黄',2,3,2,3);
insert into test_a values(to_Date('2015-09-10','yyyy-mm-dd'),'小红',2,4,3,4);
insert into test_a values(to_Date('2015-09-10','yyyy-mm-dd'),'小花',1,2,4,3);
insert into test_a values(to_Date('2015-09-11','yyyy-mm-dd'),'小明',2,3,1,4);
insert into test_a values(to_Date('2015-09-11','yyyy-mm-dd'),'小黄',2,4,2,1);
insert into test_a values(to_Date('2015-09-11','yyyy-mm-dd'),'小红',4,3,3,2);
insert into test_a values(to_Date('2015-09-11','yyyy-mm-dd'),'小花',2,4,4,4);
insert into test_a values(to_Date('2015-09-12','yyyy-mm-dd'),'小明',1,2,3,3);
insert into test_a values(to_Date('2015-09-12','yyyy-mm-dd'),'小黄',2,3,2,2);
insert into test_a values(to_Date('2015-09-12','yyyy-mm-dd'),'小红',1,3,1,2);
insert into test_a values(to_Date('2015-09-12','yyyy-mm-dd'),'小花',3,2,2,3);
insert into test_b values(to_Date('2015-09-10','yyyy-mm-dd'),'小红','a',1);
insert into test_b values(to_Date('2015-09-10','yyyy-mm-dd'),'小花','b',1);
insert into test_b values(to_Date('2015-09-11','yyyy-mm-dd'),'小红','a',2);
insert into test_b values(to_Date('2015-09-12','yyyy-mm-dd'),'小红','a',1);
insert into test_b values(to_Date('2015-09-12','yyyy-mm-dd'),'小明','b',1);
commit;
--输出查询结果
select A.t_date,A.name,A.A,A.B,(case when B.t_type='a' then A.C-B.t_number else A.C end) C,
(case when B.t_type='b' then A.D-B.t_number else A.D end) D
from test_a A ,test_b B
where A.t_date=B.t_date(+)
and A.name=B.name(+)
order by t_Date,decode(name,'小明',1,'小黄',2,'小红',3,'小花',4)
--这里只是输入查询结果,并未改变表A中的数据,如果要改变表A的数据,请用update