两张表 A, B 字段一样如下 f1 f2 f3 f4 timeA 表数据 f1 f2 f3 f4 time
1 2 12:00
3 4 13:00
5 7 14:00B 表数据 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
相当于合并两个表,空数据的地方取另一个表的数据来填充。我用的是orcal数据库。真心求一sql语句,非常感谢。分不够我可以再开贴加
1 2 12:00
3 4 13:00
5 7 14:00B 表数据 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
相当于合并两个表,空数据的地方取另一个表的数据来填充。我用的是orcal数据库。真心求一sql语句,非常感谢。分不够我可以再开贴加
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 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 f1,f2,f3,f4,time from a
union all
select ... from b
)
group by time
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
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
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
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
大家就不考虑一下,如果 time 值有重复的吗?
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