三表只有一个字段不相同，部分（前3个）字段的值完全相等，进行表连接形成新表。
本帖最后由 bbaizz 于 2013-06-04 16:26:01 编辑 一、原始数据
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
99 28 36 17
表3结构：
A1 A2 A3 C4
66 77 88 23
22 33 44 81
55 99 51 37
二、期望值,连接出来的新表(D1为A4+B4+C4)
A1 A2 A3 A4 B4 C4 D1 F1
22 33 44 55 88 81 224 是
66 77 88 0 99 23 122 否
99 28 36 0 17 0 17 否
55 99 51 0 0 37 37 是
F1满意如下其中之一判定为“是”
A4（>0）+B4（>0）>=20
A4（>0）+C4（>0）>=20
B4（>0）+C4（>0）>=20
B4（>0）++B4（>0）>+C4（>0）>=20
其他为“否”
最好两步查询可以实现。
[解决办法]
if object_id('[TB1]') is not null drop table [TB1]
go
create table [TB1] (A1 int,A2 int,A3 int,A4 int)
insert into [TB1]
select 22,33,44,55
if object_id('[TB2]') is not null drop table [TB2]
go
create table [TB2] (A1 int,A2 int,A3 int,B4 int)
insert into [TB2]
select 66,77,88,99 union all
select 22,33,44,88 union all
select 99,28,36,17
if object_id('[TB3]') is not null drop table [TB3]
go
create table [TB3] (A1 int,A2 int,A3 int,C4 int)
insert into [TB3]
select 66,77,88,23 union all
select 22,33,44,81 union all
select 55,99,51,37
select * from [TB1]
select * from [TB2]
select * from [TB3]
;WITH TT
AS ( SELECT A1 ,
A2 ,
A3
FROM dbo.TB1
UNION
SELECT A1 ,
A2 ,
A3
FROM dbo.TB2
UNION
SELECT A1 ,
A2 ,
A3
FROM dbo.TB3
),
T1
AS ( SELECT TT.a1 ,
tt.a2 ,
tt.a3 ,
ISNULL(tb1.a4, 0) AS a4 ,
ISNULL(tb2.b4, 0) AS b4 ,
ISNULL(tb3.c4, 0) AS c4
FROM TT
LEFT JOIN TB1 ON TT.A1 = dbo.TB1.A1
AND TT.A2 = dbo.TB1.A2
AND TT.A3 = dbo.TB1.A3
LEFT JOIN TB2 ON TT.A1 = dbo.TB2.A1
AND TT.A2 = dbo.TB2.A2
AND TT.A3 = dbo.TB2.A3
LEFT JOIN TB3 ON TT.A1 = dbo.TB3.A1
AND TT.A2 = dbo.TB3.A2
AND TT.A3 = dbo.TB3.A3
)
SELECT * ,
a4+b4+c4 AS d1,
CASE WHEN A4 + b4 >= 20
AND a4 > 0
AND b4 > 0 THEN '是'
ELSE CASE WHEN A4 + c4 >= 20
AND a4 > 0
AND c4 > 0 THEN '是'
ELSE CASE WHEN b4 + c4 >= 20
AND b4 > 0
AND c4 > 0 THEN '是'
ELSE '否'
END
END
END AS F1
FROM t1
/*
a1a2a3a4b4c4d1F1
223344558881224是
559951003737否
66778809923122是
992836017017否*/
if object_id('[TB1]') is not null drop table [TB1]
go
create table [TB1] (A1 int,A2 int,A3 int,A4 int)
insert into [TB1]
select 22,33,44,55
if object_id('[TB2]') is not null drop table [TB2]
go
create table [TB2] (A1 int,A2 int,A3 int,B4 int)
insert into [TB2]
select 66,77,88,99 union all
select 22,33,44,88 union all
select 99,28,36,17
if object_id('[TB3]') is not null drop table [TB3]
go
create table [TB3] (A1 int,A2 int,A3 int,C4 int)
insert into [TB3]
select 66,77,88,23 union all
select 22,33,44,81 union all
select 55,99,51,37
select A1,A2,A3,
isnull(MAX(A4),0) as A4,isnull(MAX(B4),0) as B4,isnull(MAX(C4),0) as C4,SUM(A4)+sum(B4)+SUM(C4) as D1,
case when
(
case
when sum(case when A4>0 then A4 else 0 end)>0 and sum(case when B4>0 then B4 else 0 end)>0
then sum(case when A4>0 then A4 else 0 end)+sum(case when B4>0 then B4 else 0 end)
when sum(case when A4>0 then A4 else 0 end)>0 and sum(case when C4>0 then C4 else 0 end)>0
then sum(case when A4>0 then A4 else 0 end)+sum(case when C4>0 then C4 else 0 end)
when sum(case when C4>0 then C4 else 0 end)>0 and sum(case when B4>0 then B4 else 0 end)>0
then sum(case when C4>0 then C4 else 0 end)+sum(case when B4>0 then B4 else 0 end)
when sum(case when A4>0 then A4 else 0 end)>0 and sum(case when B4>0 then B4 else 0 end)>0 and sum(case when C4>0 then C4 else 0 end)>0
then sum(case when A4>0 then A4 else 0 end)+sum(case when B4>0 then B4 else 0 end)+sum(case when C4>0 then C4 else 0 end)
end
)>=20 then '是' else '否' end as F1
from
(
select A1,A2,A3,A4,0 as B4,0 as C4
from TB1
union
select A1,A2,A3,0 as A4, B4,0 as C4
from TB2
union
select A1,A2,A3,0 as A4, 0 as B4,C4
from TB3
) tb
group by A1,A2,A3