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,55if 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,17if 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,37select * 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/* a1 a2 a3 a4 b4 c4 d1 F1 22 33 44 55 88 81 224 是 55 99 51 0 0 37 37 否 66 77 88 0 99 23 122 是 99 28 36 0 17 0 17 否*/
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 F1from ( 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
go
create table [TB1] (A1 int,A2 int,A3 int,A4 int)
insert into [TB1]
select 22,33,44,55if 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,17if 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,37select * 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/*
a1 a2 a3 a4 b4 c4 d1 F1
22 33 44 55 88 81 224 是
55 99 51 0 0 37 37 否
66 77 88 0 99 23 122 是
99 28 36 0 17 0 17 否*/
我的是SQL2000呀,
服务器: 消息 156,级别 15,状态 1,行 11
在关键字 'WITH' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 26
第 26 行: ',' 附近有语法错误。
我的是SQL2000呀,
服务器: 消息 156,级别 15,状态 1,行 11
在关键字 'WITH' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 26
第 26 行: ',' 附近有语法错误。
2000没有CTE,你可以用子查询或者临时表替换CTE的部分
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 F1from
(
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