现有2张表,
a表:
listcode code
123 59
234 23
245 33
b表:
xh addr phone
123 abc 123456
234 123 546547
259 124 465768
希望合并后得到c表:
listcode code addr phone
123 59 abc 123456
234 23 123 546547
245 33 null null
259 null 124 465768
请各位大人指点!!!
------解决方案--------------------
create table a(listcode int,code int)
insert a select 123,59
union all select 234,23
union all select 245,33
create table b(xh int,addr varchar(20),phone varchar(10))
insert b select 123, 'abc ', '123456 '
union all select 234, '123 ', '546547 '
union all select 259, '124 ', '465768 '
select isnull(a.listcode,b.xh),code,addr,phone from a full outer join b
on a.listcode=b.xh
drop table a,b
code addr phone
----------- ----------- -------------------- ----------
123 59 abc 123456
234 23 123 546547
245 33 NULL NULL
259 NULL 124 465768
（所影响的行数为 4 行）
------解决方案--------------------
select all.listcode,a.code,b.addr,b.phone
from (select listcode from A union select xh from b) as all left join a on all.listcode = a.listcode left join b on
all.listcode = b.xh
------解决方案--------------------
select a.*,b.addr,b.phone from A a left join B b on a.listcode=b.xh
union
select a.xh,b.code,a.addr,a.phone from B b left join A a on a.xh=b.listcode
------解决方案--------------------
full join
------解决方案--------------------
full join = left join + right join
这个写法最简单
------解决方案--------------------
select isnull(a.listcode,b.xh),code,addr,phone
from a
full join b on a.listcode=b.xh