表一
name data_
A 4
b 6
A 2
B 3
C 4
D 5
表二
name data_
A 3
b 5
B 2
D 1
现在要求得到
name data_ data_2
A 4 3
b 6 5
A 2 3
B 3 2
C 4
D 5 1
D 5
------解决方案--------------------
select a.name,a.data_,b.data_ as data_2
from 表一 a
left join 表二 b on a.name=b.name
------解决方案--------------------
create table 表一(name varchar(10),data_ int)
insert into 表一
select 'A',4 union all
select 'b',6 union all
select 'A',2 union all
select 'B',3 union all
select 'C',4 union all
select 'D',5
create table 表二(name varchar(10),data_ int)
insert into 表二
select 'A',3 union all
select 'b',5 union all
select 'B',2 union all
select 'D',1
select a.name,a.data_,b.data_ 'data_2'
from 表一 a
left join 表二 b on a.name=b.name collate Chinese_PRC_CS_AS
/*
name data_ data_2
---------- ----------- -----------
A 4 3
b 6 5
A 2 3
B 3 2
C 4 NULL
D 5 1
(6 row(s) affected)
*/
------解决方案--------------------
if object_id ('T1') is not null
drop table T1
create table T1(name varchar(20),data_ int)
insert into T1
select 'A',4 union all
select 'b',6 union all
select 'A',2 union all
select 'B',3 union all
select 'C',4 union all
select 'D',5
if object_id ('T2') is not null
drop table T2
create table T2(name varchar(20),data_ int)
insert into T2
select 'A',3 union all
select 'b',5 union all
select 'B',2 union all
select 'D',1
select a.name,a.data_,b.data_ 'data_2'
from T1 a
left join T2 b on a.name=b.name
------解决方案--------------------
select a.name,a.data_,b.data_ as data_2
from 表一 a
left join 表二 b on a.name=b.name
------解决方案--------------------