现有A,B两个表分别为:
A表
a b c
1 GC 2
1 JK 2
3 JK 4
--------------------
B表
a b d
1 GC 6
3 GC 5
5 GC 4
怎么得到C表
a b c d
1 GC 2 6
1 JK 2 0
3 JK 4 0
2 GC 0 5
5 GC 0 4
请高手指点:)
在线等..........
A表
a b c
1 GC 2
1 JK 2
3 JK 4
--------------------
B表
a b d
1 GC 6
3 GC 5
5 GC 4
怎么得到C表
a b c d
1 GC 2 6
1 JK 2 0
3 JK 4 0
2 GC 0 5
5 GC 0 4
请高手指点:)
在线等..........
create table b(a integer,b varchar2(10),d integer);
insert into a values(1,'GC','2');
insert into a values(1,'JK','2');
insert into a values(3,'JK','4');
insert into b values(1,'GC',6);
insert into b values(3,'GC',5);
insert into b values(5,'GC',4);select t1.a,t1.b,nvl(c,0) c,nvl(d,0) d from a t1,b t2 where t1.a=t2.a(+) and t1.b=t2.b(+)
union
select t2.a,t2.b,nvl(c,0) c,nvl(d,0) d from a t1,b t2 where t1.a(+)=t2.a and t1.b(+)=t2.b; A B C D
---------- ---------- ---------- ----------
1 GC 2 6
1 JK 2 0
3 GC 0 5
3 JK 4 0
5 GC 0 4
a b c
1 GC 2
1 JK 2
3 JK 4
--------------------
B表
a b d
1 GC 6
3 GC 5
5 GC 4
怎么得到C表
a b c d
1 GC 2 6
1 JK 2 0
3 JK 4 0
2 GC 0 5 <-------------2怎么来的? 3 吧?
5 GC 0 4
select nvl2(ta.a,ta.a,tb.a) a,
nvl2(ta.b,ta.b,tb.b) b,
nvl2(ta.c,ta.c,0) c,
nvl2(tb.d,tb.d,0) d
from
(
select '1' a,'GC' b,'2' c from dual union all
select '1','JK','2' from dual union all
select '3','JK','4' from dual
) ta
full outer join (
select * from
(
select '1' a,'GC' b,'6' d from dual union all
select '3','GC','5' from dual union all
select '5','GC','4' from dual
)
) tb on ta.a=tb.a and ta.b=tb.b
order by 1------------------------------------------------
A B C D
1 1 GC 2 6
2 1 JK 2 0
3 3 GC 0 5
4 3 JK 4 0
5 5 GC 0 4