Update--建立测试环境 Create Table cp225(pp1 Int, pp2 Int,pp3 Int, pp4 Int, pp5 Int, number1 Int) Insert cp225(pp1, pp2, pp3, pp4, pp5) Select 2, 3, 4, 5, 6 Union All Select 4, 5, 6, 7, 8 Union All Select 11,12,13,14,18 Union All Select 15,16,17,18,19 Union All Select 1, 4, 7, 10,15 --测试 Update A Set number1=B.number1 from cp225 A Inner Join (Select pp1,pp2,pp3,pp4,pp5, number1=SUM((Case When pp1%3=2 Then 1 Else 0 End)+ (Case When pp2%3=2 Then 1 Else 0 End)+ (Case When pp3%3=2 Then 1 Else 0 End)+ (Case When pp4%3=2 Then 1 Else 0 End)+ (Case When pp5%3=2 Then 1 Else 0 End)) from cp225 Group By pp1,pp2,pp3,pp4,pp5) B On A.pp1=B.pp1 And A.pp2=B.pp2 And A.pp3=B.pp3 And A.pp4=B.pp4 And A.pp5=B.pp5Select * from cp225 --删除测试环境 Drop Table cp225 --结果 /* pp1 pp2 pp3 pp4 pp5 number1 2 3 4 5 6 2 4 5 6 7 8 2 11 12 13 14 18 2 15 16 17 18 19 1 1 4 7 10 15 0 */
删除--建立测试环境 Create Table cp225(pp1 Int, pp2 Int,pp3 Int, pp4 Int, pp5 Int, number1 Int) Insert cp225(pp1, pp2, pp3, pp4, pp5) Select 2, 3, 4, 5, 6 Union All Select 4, 5, 6, 7, 8 Union All Select 11,12,13,14,18 Union All Select 15,16,17,18,19 Union All Select 1, 4, 7, 10,15 --测试 Select * from cp225Delete A from cp225 A Inner Join (Select pp1,pp2,pp3,pp4,pp5, number1=SUM((Case When pp1%3=2 Then 1 Else 0 End)+ (Case When pp2%3=2 Then 1 Else 0 End)+ (Case When pp3%3=2 Then 1 Else 0 End)+ (Case When pp4%3=2 Then 1 Else 0 End)+ (Case When pp5%3=2 Then 1 Else 0 End)) from cp225 Group By pp1,pp2,pp3,pp4,pp5) B On A.pp1=B.pp1 And A.pp2=B.pp2 And A.pp3=B.pp3 And A.pp4=B.pp4 And A.pp5=B.pp5 Where B.number1=2Select * from cp225 --删除测试环境 Drop Table cp225 --结果 /* --删除前 pp1 pp2 pp3 pp4 pp5 number1 2 3 4 5 6 NULL 4 5 6 7 8 NULL 11 12 13 14 18 NULL 15 16 17 18 19 NULL 1 4 7 10 15 NULL--删除后 15 16 17 18 19 NULL 1 4 7 10 15 NULL */
--创建测试环境 create table pp225 ( pp1 int,pp2 int,pp3 int,pp4 int,pp5 int,number1 int ) insert pp225 select 2,3,4,5,6,null union select 4,5,6,7,8,null union select 11,12,13,14,18,null union select 15,16,17,18,19,null union select 1,4,7,10,15,null--测试 update pp225 set number1=(case when pp1%3=2 then 1 else 0 end)+ (case when pp2%3=2 then 1 else 0 end)+ (case when pp3%3=2 then 1 else 0 end)+ (case when pp4%3=2 then 1 else 0 end)+ (case when pp5%3=2 then 1 else 0 end)select * from pp225--删除测试环境 drop table pp225--结果 /*pp1 pp2 pp3 pp4 pp5 number1 ----------- ----------- ----------- ----------- ----------- ----------- 1 4 7 10 15 0 2 3 4 5 6 2 4 5 6 7 8 2 11 12 13 14 18 2 15 16 17 18 19 1 */
晕,写复杂了。 --建立测试环境 Create Table cp225(pp1 Int, pp2 Int,pp3 Int, pp4 Int, pp5 Int, number1 Int) Insert cp225(pp1, pp2, pp3, pp4, pp5) Select 2, 3, 4, 5, 6 Union All Select 4, 5, 6, 7, 8 Union All Select 11,12,13,14,18 Union All Select 15,16,17,18,19 Union All Select 1, 4, 7, 10,15 --测试 --Update Update cp225 Set number1= (Case When pp1%3=2 Then 1 Else 0 End)+ (Case When pp2%3=2 Then 1 Else 0 End)+ (Case When pp3%3=2 Then 1 Else 0 End)+ (Case When pp4%3=2 Then 1 Else 0 End)+ (Case When pp5%3=2 Then 1 Else 0 End)Select * from cp225 --Delete Delete from cp225 Where ((Case When pp1%3=2 Then 1 Else 0 End)+ (Case When pp2%3=2 Then 1 Else 0 End)+ (Case When pp3%3=2 Then 1 Else 0 End)+ (Case When pp4%3=2 Then 1 Else 0 End)+ (Case When pp5%3=2 Then 1 Else 0 End))=2Select * from cp225 --删除测试环境 Drop Table cp225 --结果 /* pp1 pp2 pp3 pp4 pp5 number1 2 3 4 5 6 2 4 5 6 7 8 2 11 12 13 14 18 2 15 16 17 18 19 1 1 4 7 10 15 0pp1 pp2 pp3 pp4 pp5 number1 15 16 17 18 19 NULL 1 4 7 10 15 NULL */
Create Table cp225(pp1 Int, pp2 Int,pp3 Int, pp4 Int, pp5 Int, number1 Int)
Insert cp225(pp1, pp2, pp3, pp4, pp5)
Select 2, 3, 4, 5, 6 Union All
Select 4, 5, 6, 7, 8 Union All
Select 11,12,13,14,18 Union All
Select 15,16,17,18,19 Union All
Select 1, 4, 7, 10,15
--测试
Update A Set number1=B.number1
from cp225 A
Inner Join
(Select pp1,pp2,pp3,pp4,pp5,
number1=SUM((Case When pp1%3=2 Then 1 Else 0 End)+
(Case When pp2%3=2 Then 1 Else 0 End)+
(Case When pp3%3=2 Then 1 Else 0 End)+
(Case When pp4%3=2 Then 1 Else 0 End)+
(Case When pp5%3=2 Then 1 Else 0 End))
from cp225
Group By pp1,pp2,pp3,pp4,pp5) B
On A.pp1=B.pp1 And A.pp2=B.pp2 And A.pp3=B.pp3 And A.pp4=B.pp4 And A.pp5=B.pp5Select * from cp225
--删除测试环境
Drop Table cp225
--结果
/*
pp1 pp2 pp3 pp4 pp5 number1
2 3 4 5 6 2
4 5 6 7 8 2
11 12 13 14 18 2
15 16 17 18 19 1
1 4 7 10 15 0
*/
Create Table cp225(pp1 Int, pp2 Int,pp3 Int, pp4 Int, pp5 Int, number1 Int)
Insert cp225(pp1, pp2, pp3, pp4, pp5)
Select 2, 3, 4, 5, 6 Union All
Select 4, 5, 6, 7, 8 Union All
Select 11,12,13,14,18 Union All
Select 15,16,17,18,19 Union All
Select 1, 4, 7, 10,15
--测试
Select * from cp225Delete A from cp225 A
Inner Join
(Select pp1,pp2,pp3,pp4,pp5,
number1=SUM((Case When pp1%3=2 Then 1 Else 0 End)+
(Case When pp2%3=2 Then 1 Else 0 End)+
(Case When pp3%3=2 Then 1 Else 0 End)+
(Case When pp4%3=2 Then 1 Else 0 End)+
(Case When pp5%3=2 Then 1 Else 0 End))
from cp225
Group By pp1,pp2,pp3,pp4,pp5) B
On A.pp1=B.pp1 And A.pp2=B.pp2 And A.pp3=B.pp3 And A.pp4=B.pp4 And A.pp5=B.pp5
Where B.number1=2Select * from cp225
--删除测试环境
Drop Table cp225
--结果
/*
--删除前
pp1 pp2 pp3 pp4 pp5 number1
2 3 4 5 6 NULL
4 5 6 7 8 NULL
11 12 13 14 18 NULL
15 16 17 18 19 NULL
1 4 7 10 15 NULL--删除后
15 16 17 18 19 NULL
1 4 7 10 15 NULL
*/
create table pp225
(
pp1 int,pp2 int,pp3 int,pp4 int,pp5 int,number1 int
)
insert pp225
select 2,3,4,5,6,null union
select 4,5,6,7,8,null union
select 11,12,13,14,18,null union
select 15,16,17,18,19,null union
select 1,4,7,10,15,null--测试
update pp225 set number1=(case when pp1%3=2 then 1 else 0 end)+
(case when pp2%3=2 then 1 else 0 end)+
(case when pp3%3=2 then 1 else 0 end)+
(case when pp4%3=2 then 1 else 0 end)+
(case when pp5%3=2 then 1 else 0 end)select * from pp225--删除测试环境
drop table pp225--结果
/*pp1 pp2 pp3 pp4 pp5 number1
----------- ----------- ----------- ----------- ----------- -----------
1 4 7 10 15 0
2 3 4 5 6 2
4 5 6 7 8 2
11 12 13 14 18 2
15 16 17 18 19 1
*/
--建立测试环境
Create Table cp225(pp1 Int, pp2 Int,pp3 Int, pp4 Int, pp5 Int, number1 Int)
Insert cp225(pp1, pp2, pp3, pp4, pp5)
Select 2, 3, 4, 5, 6 Union All
Select 4, 5, 6, 7, 8 Union All
Select 11,12,13,14,18 Union All
Select 15,16,17,18,19 Union All
Select 1, 4, 7, 10,15
--测试
--Update
Update cp225 Set number1=
(Case When pp1%3=2 Then 1 Else 0 End)+
(Case When pp2%3=2 Then 1 Else 0 End)+
(Case When pp3%3=2 Then 1 Else 0 End)+
(Case When pp4%3=2 Then 1 Else 0 End)+
(Case When pp5%3=2 Then 1 Else 0 End)Select * from cp225
--Delete
Delete from cp225 Where
((Case When pp1%3=2 Then 1 Else 0 End)+
(Case When pp2%3=2 Then 1 Else 0 End)+
(Case When pp3%3=2 Then 1 Else 0 End)+
(Case When pp4%3=2 Then 1 Else 0 End)+
(Case When pp5%3=2 Then 1 Else 0 End))=2Select * from cp225
--删除测试环境
Drop Table cp225
--结果
/*
pp1 pp2 pp3 pp4 pp5 number1
2 3 4 5 6 2
4 5 6 7 8 2
11 12 13 14 18 2
15 16 17 18 19 1
1 4 7 10 15 0pp1 pp2 pp3 pp4 pp5 number1
15 16 17 18 19 NULL
1 4 7 10 15 NULL
*/