select case when a.id is null then b.ID else a.ID end as ID,a.F1,b.F2 from 表1 a full outer join 表2 b on a.ID = b.ID 试试看
select a.id,a.f1,b.f2 from tab1 a,tab2 b where b.id = a.id(+) union select id,null f1,f2 from tab2 where id not in ( select id from tab1 )
in oracle8i: select id,f1,null f2 from tab1 union select id,null f1,f2 from tab2 /
谢bigmouseg(bigmouse)兄,一语惊醒梦中人啊,马上给分,等着啊。 To Others:用Union,不行,我要的是两个表Id相同的和成一条记录。
bigmouse(bigmouse):又发现新问题,那Id如何显示,我要唯一的一列显示Id,即 Id f1 f2 ------------- 01 f11 02 f21 03 f13 f23 .... 再向各位求助
想一个复杂的! 先说一个“子查询”如下 Select tab1.id from tab1,tab2 where tab1.id=tab2.id 找到一个共同的id,替换到下面的语句中Select tab1.id,tab1.f1,tab2.f2 from tab1,tab2 where tab1.id=tab2.id //同者都有 union Select id,f1,null from tab1 where id no in(子查询) //只tab1有 union Select id,null,f2 from tab2 where id no in(子查询) //只tab2有 order by 1
同意 hughie(雨神) 的说法
select [表1].ID,[表1].F1,[表2].F2 from [表1] inner join [表2] on [表1].ID=[表2].ID union all select [表1].ID,[表1].F1,[表2].F2 from [表1] left join [表2] on [表1].ID=[表2].ID where [表2].ID is NULL union all select [表2].ID,[表1].F1,[表2].F2 from [表2] left join [表1] on [表2].ID=[表1].ID where [表1].ID is NULL
谁说union不能用呀! in oracle8i: select tab2.id,tab1.f1,tab2.f2 from tab1,tab2 where tab1.id(+)=tab2.id union select tab1.id,tab1.f1,tab2.f2 from tab1,tab2 where tab1.id=tab2.id(+)
from 表1 a full outer join 表2 b on a.ID = b.ID
试试看
from tab1 a,tab2 b where b.id = a.id(+)
union
select id,null f1,f2
from tab2 where id not in
(
select id from tab1
)
select id,f1,null f2 from tab1
union
select id,null f1,f2 from tab2
/
To Others:用Union,不行,我要的是两个表Id相同的和成一条记录。
Id f1 f2
-------------
01 f11
02 f21
03 f13 f23
....
再向各位求助
先说一个“子查询”如下
Select tab1.id from tab1,tab2 where tab1.id=tab2.id
找到一个共同的id,替换到下面的语句中Select tab1.id,tab1.f1,tab2.f2 from tab1,tab2 where tab1.id=tab2.id //同者都有
union
Select id,f1,null from tab1 where id no in(子查询) //只tab1有
union
Select id,null,f2 from tab2 where id no in(子查询) //只tab2有
order by 1
from [表1] inner join [表2] on [表1].ID=[表2].ID
union all
select [表1].ID,[表1].F1,[表2].F2
from [表1] left join [表2] on [表1].ID=[表2].ID
where [表2].ID is NULL
union all
select [表2].ID,[表1].F1,[表2].F2
from [表2] left join [表1] on [表2].ID=[表1].ID
where [表1].ID is NULL
in oracle8i:
select tab2.id,tab1.f1,tab2.f2 from tab1,tab2 where tab1.id(+)=tab2.id
union
select tab1.id,tab1.f1,tab2.f2 from tab1,tab2 where tab1.id=tab2.id(+)
却要用union连接,
浪费时间。