oracle结果相减_oracle两张表满足某个条件时，表中满足条件的那一行的某列相减...

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

--建立测试表

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