两张表 A, B 字段一样如下
f1 f2 f3 f4 time
A 表数据
f1 f2 f3 f4 time
1 2 12:00
3 4 13:00
B 表数据
f1 f2 f3 f4 time
12 13 12:00
15 16
得到查询结果如下：
f1 f2 f3 f4 time
1 2 12 13 12:00
3 15 4 16 13:00
相当于合并两个表，空数据的地方取另一个表的数据来填充。
我用的是orcal数据库。真心求一sql语句，非常感谢。
orcal 版还有一贴，分可以一并给。
------解决方案--------------------
oracle用SQL语句能行吗？有些语法不一样的
------解决方案--------------------
ORACLE和SQL的语法还是不同的，在ORACLE看吧！SQL给两个表做个标识列，然后join连接判断下NULL就可以了。
------解决方案--------------------
sql的实现，oracle不会
- SQL code
create table aaa(f1 int,f2 int,f3 int,f4 int,time time)create table bbb(f1 int,f2 int,f3 int,f4 int,time time)insert into aaa select 1,2,null,null,'12:00'union all select 3,null,4,null,'13:00'insert into bbb select null,null,13,14,'12:00'union all select null,15,null,16,'13:00'goselect f1=(case when aaa.f1 is null then bbb.f1 else aaa.f1 end), f2=(case when aaa.f2 is null then bbb.f2 else aaa.f2 end), f3=(case when aaa.f3 is null then bbb.f3 else aaa.f3 end), f4=(case when aaa.f4 is null then bbb.f4 else aaa.f4 end), aaa.time from aaa join bbb on aaa.time=bbb.time结果为：f1 f2 f3 f4 time1 2 13 14 12:00:00.00000003 15 4 16 13:00:00.0000000
------解决方案--------------------
查oracle也有coalesce函数,功能刚好与SQL相同,
- SQL code
create table taba(f1 int, f2 int, f3 int, f4 int, times varchar(7))insert into tabaselect 1,2,null,null,'12:00' union allselect 3,null,4,null,'13:00'create table tabb(f1 int, f2 int, f3 int, f4 int, times varchar(7))insert into tabbselect null,null,12,13,'12:00' union allselect null,15,null,16,'13:00'select coalesce(a.f1,b.f1) f1,coalesce(a.f2,b.f2) f2,coalesce(a.f3,b.f3) f3,coalesce(a.f4,b.f4) f4,a.timesfrom taba ainner join tabb bon a.times=b.timesf1 f2 f3 f4 times----------- ----------- ----------- ----------- -------1 2 12 13 12:003 15 4 16 13:00(2 row(s) affected)
------解决方案--------------------
------解决方案--------------------
- SQL code
-- ORACLE的方法，你试试看create table aaa(f1 int,f2 int,f3 int,f4 int,time time)create table bbb(f1 int,f2 int,f3 int,f4 int,time time)insert into aaa select 1,2,null,null,'12:00'union all select 3,null,4,null,'13:00'insert into bbb select null,null,13,14,'12:00'union all select null,15,null,16,'13:00'go--方法1select f1 = nvl(a.f1,b.f1), f2 = nvl(a.f2,b.f2), f3 = nvl(a.f3,b.f3), f4 = nvl(a.f4,b.f4), time = nvl(a.time , b.time)from (select rownum as ID,* from aaa) a join (select rownum as ID,* from bbb) b on a.ID = b.ID --方法2 select f1 = nvl(a.f1,b.f1), f2 = nvl(a.f2,b.f2), f3 = nvl(a.f3,b.f3), f4 = nvl(a.f4,b.f4), time = nvl(a.time , b.time)from (select dense_rank() over (order by time) as ID,* from aaa) a join (select dense_rank() over (order by time) as ID,* from bbb) b on a.ID = b.IDdrop table aaa,bbb
------解决方案--------------------