17,377
社区成员
发帖
与我相关
我的任务
分享
A 表数据
f1 f2 f3 f4 time
1 2 12:00
3 4 13:00
5 7 14:00
B 表数据
f1 f2 f3 f4 time
12 13 12:00
15 16 13:00
得到查询结果如下:
f1 f2 f3 f4 time
1 2 12 13 12:00
3 15 4 16 13:00
5 7 14:00
create table t1(f1 varchar2(20),f2 varchar2(20),f3 varchar2(20),f4 varchar2(20),time varchar2(20) );
insert into t1 values (1,2,'','','12:00');
insert into t1 values (3,'','4','','13:00');
insert into t1 values (5,7,'','','14:00');
insert into t1 values ('','',1,'','14:00');
create table t2(f1 varchar2(20),f2 varchar2(20),f3 varchar2(20),f4 varchar2(20),time varchar2(20) );
insert into t2 values ('','',12,13,'12:00');
insert into t2 values ('',15,'',16,'13:00');
insert into t2 values (11,'','',12,'15:00');
insert into t2 values ('',1,3,'','15:00');
commit;
select wm_concat(a.f1||b.f1)f1,
wm_concat(a.f2||b.f2)f2 ,
wm_concat(a.f3||b.f3)f3 ,
wm_concat(a.f4||b.f4)f4 ,
decode(a.time,'',b.time,a.time) time
from t1 a full join t2 b on a.time=b.time
group by decode(a.time,'',b.time,a.time)
f1 f2 f3 f4 time
--------------------------------------------
1 1 2 12 13 12:00
2 3 15 4 16 13:00
3 5 7 1 14:00
4 11 1 3 12 15:00
create table t1(f1 varchar2(20),f2 varchar2(20),f3 varchar2(20),f4 varchar2(20),time varchar2(20) );
insert into t1 values (1,2,'','','12:00');
insert into t1 values (3,'','4','','13:00');
insert into t1 values (5,7,'','','14:00');
create table t2(f1 varchar2(20),f2 varchar2(20),f3 varchar2(20),f4 varchar2(20),time varchar2(20) );
insert into t2 values ('','',12,13,'12:00');
insert into t2 values ('',15,'',16,'13:00');
insert into t2 values (11,'','',12,'15:00');
commit;
select a.f1||b.f1 f1,a.f2||b.f2 f2 ,a.f3||b.f3 f3 ,a.f4||b.f4 f4 ,decode(a.time,'',b.time,a.time) time
from t1 a full join t2 b on a.time=b.time
order by a.time
f1 f2 f3 f4 time
--------------------------------------------
1 1 2 12 13 12:00
2 3 15 4 16 13:00
3 5 7 14:00
4 11 12 15:00
create table TT1(f1 varchar2(20),f2 varchar2(20),f3 varchar2(20),f4 varchar2(20),time varchar2(20) );
insert into TT1
select '1' f1,'2' f2,'' f3,'' f4 ,'12:00' time from dual
union select '3' ,'', '4','', '13:00' from dual
union select '5','7','','','14:00' from dual;
create table TT2(f1 varchar2(20),f2 varchar2(20),f3 varchar2(20),f4 varchar2(20),time varchar2(20) );
insert into TT2
select '' f1,'' f2,'12' f3,'13' f4,'12:00' time from dual
union select '','15','','16','13:00' from dual;
commit;
--查询
select a.f1||b.f1,a.f2||b.f2,a.f3||b.f3,a.f4||b.f4,a.time
from tt1 a full join tt2 b on a.time=b.time
order by a.time
f1 f2 f3 f4 time
--------------------------------------------
1 1 2 12 13 12:00
2 3 15 4 16 13:00
3 5 7 14:00
create table TT1(f1 varchar2(20),f2 varchar2(20),f3 varchar2(20),f4 varchar2(20),time varchar2(20) );
insert into TT1
select '1' f1,'2' f2,'' f3,'' f4 ,'12:00' time from dual
union select '3' ,'', '4','', '13:00' from dual
union select '5','7','','','14:00' from dual;
create table TT2(f1 varchar2(20),f2 varchar2(20),f3 varchar2(20),f4 varchar2(20),time varchar2(20) );
insert into TT2
select '' f1,'' f2,'12' f3,'13' f4,'12:00' time from dual
union select '','15','','16','13:00' from dual;
commit;
select sum(F1) F1,sum(F2) F2,sum(F3) F3,sum(F4) F4,time from (
select case when a.F1 is null then b.F1 end F1,
case when a.F2 is null then b.F2 end F2,
case when a.F3 is null then b.F3 end F3,
case when a.F4 is null then b.F4 end F4,a.time from tt1 a left outer join tt2 b
on a.time=b.time
union
select case when b.F1 is null then a.F1 end F1,
case when b.F2 is null then a.F2 end F2,
case when b.F3 is null then a.F3 end F3,
case when b.F4 is null then a.F4 end F4,a.time from tt1 a left outer join tt2 b
on a.time=b.time) group by time
select max(f1),
max(f2),
max(f3),
max(f4),
time from
(select f1 ,f2 ,f3 ,f4 ,time from A
union all
select f1 ,f2 ,f3 ,f4 ,time from B)
group by time
select decode(sum(to_number(f1)),0,null),
decode(sum(to_number(f2)),0,null),
decode(sum(to_number(f3)),0,null),
decode(sum(to_number(f4)),0,null),
time from
(select f1 ,f2 ,f3 ,f4 ,time from A
union all
select f1 ,f2 ,f3 ,f4 ,time from B)
group by time
select nvl(a.f1,b.f1)f1,nvl(a.f2,b.f2)f2,nvl(a.f3,b.f3)f3,nvl(a.f4,b.f4)f4,a.time from A a
left join B b on a.time=b.time order by a.time