100 分 求一sql语句解决方法

100 分 求一sql语句
两张表 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)
[解决办法]
探讨


查oracle也有coalesce函数,功能刚好与SQL相同,
SQL code

create table taba
(f1 int, f2 int, f3 int, f4 int, times varchar(7))

insert into taba
select 1,2,null,null,'12:00' union all
select 3,null,4,null,'13:00'
……

[解决办法]
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
------解决方案-------------------- 
 
SQL code
--oracleselect nvl(m.f1,n.f1) f1,       nvl(m.f2,n.f2) f2,       nvl(m.f3,n.f3) f3,       nvl(m.f4,n.f4) f4,       nvl(m.time,n.time) timefrom(         select a.* , row_number() over(order by f1) px from a) mfull join(  select b.* , row_number() over(order by f3) px from b) non m.id = n.id--sql server 2005select isnull(m.f1,n.f1) f1,       isnull(m.f2,n.f2) f2,       isnull(m.f3,n.f3) f3,       isnull(m.f4,n.f4) f4,       isnull(m.time,n.time) timefrom(         select a.* , row_number() over(order by f1) px from a) mfull join(  select b.* , row_number() over(order by f3) px from b) non m.id = n.id