当前位置: 代码迷 >> Sql Server >> 表合并的有关问题
  详细解决方案

表合并的有关问题

热度:59   发布时间:2016-04-27 20:43:26.0
表合并的问题?
现有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
  相关解决方案