A表
A B
101 2.5
102
103 2.6
104 B表
C D
101 2.6
102 3
103
如何得结果:
A B C D
101 2.5 101 2.6
102 102 3
103 2.6 103
104
A B
101 2.5
102
103 2.6
104 B表
C D
101 2.6
102 3
103
如何得结果:
A B C D
101 2.5 101 2.6
102 102 3
103 2.6 103
104
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a(A int, B float)
insert into #a
select 101, 2.5 union all
select 102, null union all
select 103, 2.6 union all
select 104, null
--> 测试数据:#b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(C int, D float)
insert into #b
select 101, 2.6 union all
select 102, 3 union all
select 103, nullselect * from #a a full join #b b on a.A=b.C/*
A B C D
----------- ---------------------- ----------- ----------------------
101 2.5 101 2.6
102 NULL 102 3
103 2.6 103 NULL
104 NULL NULL NULL
*/
select * from a left join b on a.a = b.c
A B C D
101 2.5 101 2.6
102 102 3 null
103 2.6 103 null
104 null null null还是:
A B C D
101 2.5 101 2.6
102 null 102 3
103 2.6 103 null
104 null null null
A B C D
101 2.5 101 2.6
102 null 102 3
103 2.6 103 null
104 null null null
if OBJECT_ID('#a') is not null drop table #a
create table #a(A int ,B float)
insert into #a
select 101, 2.5 union
select 102, null union
select 103, 2.6 union
select 104, null
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b(C int, D float)
insert into #b
select 101, 2.6 union all
select 102, 3 union all
select 103, nullselect * from #a left join #b on #a.A=#b.C
来迟啦。。