34,575
社区成员
发帖
与我相关
我的任务
分享
--oracle
select 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) time
from
(
select a.* , row_number() over(order by f1) px from a
) m
full join
(
select b.* , row_number() over(order by f3) px from b
) n
on m.id = n.id
--sql server 2005
select 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) time
from
(
select a.* , row_number() over(order by f1) px from a
) m
full join
(
select b.* , row_number() over(order by f3) px from b
) n
on m.id = n.id
-- 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
--方法1
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 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.ID
drop table aaa,bbb
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'
create table tabb
(f1 int, f2 int, f3 int, f4 int, times varchar(7))
insert into tabb
select null,null,12,13,'12:00' union all
select 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.times
from taba a
inner join tabb b
on a.times=b.times
f1 f2 f3 f4 times
----------- ----------- ----------- ----------- -------
1 2 12 13 12:00
3 15 4 16 13:00
(2 row(s) affected)
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
select 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 time
1 2 13 14 12:00:00.0000000
3 15 4 16 13:00:00.0000000
排好的,怎么能乱成这样子,晕
重启排一下看看
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 13:00
得到查询结果如下:
f1 f2 f3 f4 time
1 2 12 13 12:00
3 15 4 16 13:00