表A和表B结构不一样,但都含有code列和name列,code都为主键。
如:
表A 表B
code name code name
a 1 a 1
b 2 b 222
c 3 f 5最后想要的结果是:
code name
a 1
b 222
c 3
f 5
就是当A.code=B.code时,用B.name替换A.name。当A.code存在,B.code不存在时,A.code和A.name保留。当A.code不存在,B.code存在时,把B.code和B.name添加都表A中。不知道说的清楚不,谢谢。
如:
表A 表B
code name code name
a 1 a 1
b 2 b 222
c 3 f 5最后想要的结果是:
code name
a 1
b 222
c 3
f 5
就是当A.code=B.code时,用B.name替换A.name。当A.code存在,B.code不存在时,A.code和A.name保留。当A.code不存在,B.code存在时,把B.code和B.name添加都表A中。不知道说的清楚不,谢谢。
from A
full join B
on A.code=B.code
drop table ta
create table ta (code varchar(2),name varchar(2))
insert into ta select 'a','1'
union all select 'b' , '2'
union all select 'c', '3' if object_id('tb') is not null
drop table tb
create table tb (code varchar(2),name varchar(5))
insert into tb select 'a','1'
union all select 'b' , '2222'
union all select 'f', '5' select isnull(a.code,b.code) as code ,
isnull(b.name,a.name) as name from ta a
full join tb b on a.code=b.code
/*
code name
---- -----
a 1
b 2222
c 3
f 5(4 行受影响)
*/
---测试数据---
if object_id('[A]') is not null drop table [A]
go
create table [A]([code] varchar(1),[name] int)
insert [A]
select 'a',1 union all
select 'b',2 union all
select 'c',3
if object_id('[B]') is not null drop table [B]
go
create table [B]([code] varchar(1),[name] int)
insert [B]
select 'a',1 union all
select 'b',222 union all
select 'f',5
---查询---
select
isnull(a.code,b.code) as code,
name=case when a.name is not null and b.name is not null then b.name else isnull(a.name,b.name) end
from a
full join b on a.code=b.code
order by 1
---结果---
code name
---- -----------
a 1
b 222
c 3
f 5(所影响的行数为 4 行)
UNION
SELECT B.CODE,A.NAME FROM B,A WHERE B.CODE = A.CODE
--借用百年树人的数据,献丑了
---测试数据---
if object_id('[A]') is not null drop table [A]
go
create table [A]([code] varchar(1),[name] int)
insert [A]
select 'a',1 union all
select 'b',2 union all
select 'c',3
if object_id('[B]') is not null drop table [B]
go
create table [B]([code] varchar(1),[name] int)
insert [B]
select 'a',1 union all
select 'b',222 union all
select 'f',5
---查询---
select isnull(a.code,b.code) as code,
case when b.code is null then a.name
when a.code is not null and b.code is not null then b.name
when a.code is null then b.name
end as name
from a full join b ON a.code = b.code
case when b.code is null then a.name
when a.code is not null and b.code is not null then b.name
when a.code is null then b.name
end as name
from a full join b ON a.code = b.code来迟了!
这个正解!