两表只有一个字段不相同，部分（前3个）字段的值完全相等，进行表连接形成新表。

[猪扒界]

1、前提：部分A1 A2 A3字段的值完全相等。
2、表结构
表1结构：
A1 A2 A3 A4
22 33 44 55

表2结构：
A1 A2 A3 B4
66 77 88 99
22 33 44 88

3、执行过程
通过
select A1, A2, A3, A4,'' as B4 from t1
union
select A1, A2, A3, '',B4 from t2
出来的结果显示：
A1 A2 A3 A4 B4
22 33 44 55 NULL
22 33 44 NULL 88
66 77 88 NULL 99
请问这是什么原因呢，理论上相同值会去重的。

4、实际期望
实际期望值：连接出来的新表
A1 A2 A3 A4 B4
22 33 44 55 88
66 77 88 NULL 99

[MrYangkang]

--union 不去重
select A1, A2, A3, A4,'' as B4 from t1
union all
select A1, A2, A3, '',B4 from t2

[猪扒界]

引用 1 楼 ZaoLianBuXiQi 的回复:

--union 不去重
select A1, A2, A3, A4,'' as B4 from t1
union all
select A1, A2, A3, '',B4 from t2

我希望的是去重的值呀，大佬 实际期望值：连接出来的新表 A1 A2 A3 A4 B4 22 33 44 55 88 66 77 88 NULL 99

[KevinLiu]

declare @test1 table (A1 varchar(10),a2 VARCHAR(10),A3 VARCHAR(10),A4 VARCHAR(10)) declare @test2 table (A1 varchar(10),a2 VARCHAR(10),A3 VARCHAR(10),B4 VARCHAR(10)) INSERT INTO @test1 VALUES ('22 ','33','44','55') INSERT INTO @test2 VALUES ('22 ','33','44','88') INSERT INTO @test2 VALUES ('21 ','34','45','99') SELECT ISNULL(T1.A1,T2.A1) AS A1,ISNULL(T1.A1,T2.a2) AS A2, ISNULL(T1.A3,T2.A3) AS A3,A4,B4 FROM @test1 T1 FULL OUTER JOIN @test2 T2 ON T1.A1 = T2.A1 AND T1.a2 = T2.a2 AND T1.A3 = T2.A3

[哥眼神纯洁不]

with t1(a,b,c,d)as(
select 22,33,44,55
)
,tc (a,b,c,d) as(
select 66,77,88,99 union all
select 22,33,44,88
)
select b.a,b.b,b.c,coalesce(b.d,a.d),coalesce(a.e,b.e) 
from (select a,b,c,d,null e from t1)a full join (select a,b,c,null d,d e from tc)b
on a.a=b.a and a.b=b.b and a.c=b.c

[MrYangkang]

--X
select B.A1,B.A2,B.A3,(CASE WHEN A.A4 IS NULL THEN B.A4 ELSE A.A4 END)A4,
 (CASE WHEN A.B4 IS NULL THEN B.B4 ELSE A.B4 END)B4 from 
(
select A1, A2, A3, A4,'' as B4 from a
)a
full join
(
select A1, A2, A3, ''as A4,B4 from b 
)b ON a.a1=b.a1 and a.A2=b.A2 and a.A3=b.A3

[MrYangkang]

--两种解决方案
if object_id('a') is not null
drop table a
go
create table a
(
	a1 int,
	a2 int,
	a3 int,
	a4 int
)
go
if object_id('b') is not null
drop table b
create table b
(
	a1 int,
	a2 int,
	a3 int,
	b4 int
)
go
insert into a
select 22,33,44,55
go
insert into b
select 66,77,88,99 union all
select 22,33,44,88
go
select B.A1,B.A2,B.A3,
(case when A.A4 IS NULL then B.A4 else A.A4 end)A4,
(case when A.B4 IS NULL then B.B4 else A.B4 end)B4 
from 
(
select A1, A2, A3, A4,'' as B4 from A
)A
FULL join
(
select A1, A2, A3, ''as A4,B4 from B 
)B ON A.A1=b.A1 and A.A2=B.A2 and A.A3=B.A3

go
--or
with t as
(
select A1, A2, A3, A4,'' as B4 from a 
union all
select A1, A2, A3, '',B4 from b
)
select A1, A2, A3,max(A4)A4,max(B4)B4
from t
group by A1, A2, A3

[猪扒界]

引用 3 楼 SmithLiu328 的回复:

declare @test1 table (A1 varchar(10),a2 VARCHAR(10),A3 VARCHAR(10),A4 VARCHAR(10)) declare @test2 table (A1 varchar(10),a2 VARCHAR(10),A3 VARCHAR(10),B4 VARCHAR(10)) INSERT INTO @test1 VALUES ('22 ','33','44','55') INSERT INTO @test2 VALUES ('22 ','33','44','88') INSERT INTO @test2 VALUES ('21 ','34','45','99') SELECT ISNULL(T1.A1,T2.A1) AS A1,ISNULL(T1.A1,T2.a2) AS A2, ISNULL(T1.A3,T2.A3) AS A3,A4,B4 FROM @test1 T1 FULL OUTER JOIN @test2 T2 ON T1.A1 = T2.A1 AND T1.a2 = T2.a2 AND T1.A3 = T2.A3

这个最简单清晰，好用。