现有2张表,
a表:
listcode code
123 59
234 23
245 33b表:
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请各位大人指点!!!
a表:
listcode code
123 59
234 23
245 33b表:
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请各位大人指点!!!
insert a select 123,59
union all select 234,23
union all select 245,33create 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.xhdrop table a,b code addr phone
----------- ----------- -------------------- ----------
123 59 abc 123456
234 23 123 546547
245 33 NULL NULL
259 NULL 124 465768(所影响的行数为 4 行)
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 = left join + right join
这个写法最简单
from a
full join b on a.listcode=b.xh
如果要into新表,是否:
select isnull(a.listcode,b.xh),code,addr,phone into c
from a
full join b on a.listcode=b.xh
如果要into新表,是否:
select isnull(a.listcode,b.xh),code,addr,phone into c
from a
full join b on a.listcode=b.xh
==========
1.可以用b.*代替,但是会出现两个b.xh
2.可以的!但要给 isnull(a.listcode,b.xh)字段名..
select isnull(a.listcode,b.xh) as listcode ,code,addr,phone into c
from a
full join b on a.listcode=b.xh
(
select xh=isnull(a.listcode,b.xh),code,addr,phone into c
from a
full join b on a.listcode=b.xh)m