一道面试题，求解

[ztalyroey]

表a
Id fname lname
1 zhu liu
2 ma ling
3 wang hua

表b
Id age tele
1 20 32425215
3 23 543242
5 21 543543543

求 id fname lname age tele
1 zhu liu 20 32425215
2 ma ling
3 wang hua 23 543242
5 21 543543543

[ztalyroey]

看起来有点乱 大家将就将就

[kourr2004]

用full join 就可以了

[xiaoku]

select ID ,isnull(fname,'') fname
,isnull(lname,'') lname
,isnull(age,0) age
,isnull(tele,'')tele
from a
full outer join b on a.id =b.id

[青锋-SS]

declare @a table(Id int,fname varchar(8),lname varchar(8))
insert into @a
select 1,'zhu','liu'
union select 2,'ma','ling'
union select 3,'wang','hua'

declare @b table(Id int,age int,tele int)
insert into @b
select 1,20,32425215
union select 3,23,543242
union select 5,21,543543543

select * from @a;
select * from @b;

select a.id,fname,lname,age,tele from @a a inner join @b b on a.id=b.id
union select a.id,fname,lname,age,tele from @a a left join @b b on a.id=b.id
union select b.id,fname,lname,age,tele from @a a right join @b b on a.id=b.id

[青锋-SS]

复人：xiaoku(野蛮人(^v^)) ( 一星(中级)) 信誉：100 2007-04-05 16:25:05 得分:0
?
select ID ,isnull(fname,'') fname
,isnull(lname,'') lname
,isnull(age,0) age
,isnull(tele,'')tele
from a
full outer join b on a.id =b.id
------------------
full join有点小问题

[青锋-SS]

多写了一句,这样就可以:
select a.id,fname,lname,age,tele from @a a left join @b b on a.id=b.id
union select b.id,fname,lname,age,tele from @a a right join @b b on a.id=b.id

[kourr2004]

create table 表a(
Id int,
fname varchar(10),
lname varchar(10)
)

insert 表a
select 1,'zhu','liu' union all
select 2,'ma','ling' union all
select 3,'wang','hua'

create table 表b(
Id int,
age int,
tele varchar(10)
)
insert 表b
select 1,20,'32425215' union all
select 3,23,'543242' union all
select 5,21,'543543543'

select isnull(a.Id,b.Id)as Id,isnull(a.fname,'')as fname ,
isnull(a.lname,'')as lname ,isnull(b.age,'')as age ,isnull(b.tele,'')as tele
from 表a a
full join 表b b
on a.Id=b.Id order by Id

--result:
id fname lname age tele
------------------------------------------
1 zhu liu 20 32425215
2 ma ling 0
3 wang hua 23 543242
5 21 543543543

[ztalyroey]

呜呜，mysql好像不支持

[青锋-SS]

楼主是mysql的,怎么发到sql版来了?

[xiaoku]

婶婶...看到小女生了?

[xiaoku]

select isnull(a.ID,b.id) ID ,isnull(fname,'') fname
,isnull(lname,'') lname
,isnull(age,0) age
,isnull(tele,'')tele
from @a a
full outer join @b b on a.id =b.id
order by ID


ID fname lname age tele
----------- -------- -------- ----------- -----------
1 zhu liu 20 32425215
2 ma ling 0 0
3 wang hua 23 543242
5 21 543543543

（所影响的行数为 4 行）
--------------
婶婶,没问题阿.

[xiaoku]

不过改了点...isnull()

[azhai]

select * from (
select id,fname from 表a inner join 表b on 表a.ID!=表b.ID
union all
select id,o.fname+p.fname from 表a o inner join 表b p on o.ID=p.ID
) as a

[sp4]

full join 足够了

[lovedick]

select * from
表Ａ a
full join
表B b
on a.id=b.id

[kkfbai]

[SQL SERVER 2000 全连接 FULL OUTER JOIN | FULL JOIN]
SELECT * FROM a FULL JOIN b ON a.id = b.id

[gjylgmh]

select isnull(表a.id,表b.id) as id,表a.fname,表a.lname,表b.age,表b.tele
from 表a full join 表b
on 表a.id=表b.id

[qingbo_hu]

select a.fname,a.lname,b.age ,b.tele from
( a join b on a.ID= b.ID)

这样解决不就可以了吗~！