update t
set number1=(
case when not exists(select 1
from cp
where pp1=t.pp1
and pp0<t.pp0)
then 1
else 0
end
)
+
(
case when not exists(select 1
from cp
where pp2=t.pp2
and pp0<t.pp0)
then 1
else 0
end
)
+
(
case when not exists(select 1
from cp
where pp3=t.pp3
and pp0<t.pp0)
then 1
else 0
end
)
+
(
case when not exists(select 1
from cp
where pp4=t.pp4
and pp0<t.pp0)
then 1
else 0
end
)
+
(
case when not exists(select 1
from cp
where pp5=t.pp5
and pp0<t.pp0)
then 1
else 0
end
)
from cp t
where (select count(1) from cp where pp0<t.pp0)>=8
set number1=(
case when not exists(select 1
from cp
where pp1=t.pp1
and pp0<t.pp0)
then 1
else 0
end
)
+
(
case when not exists(select 1
from cp
where pp2=t.pp2
and pp0<t.pp0)
then 1
else 0
end
)
+
(
case when not exists(select 1
from cp
where pp3=t.pp3
and pp0<t.pp0)
then 1
else 0
end
)
+
(
case when not exists(select 1
from cp
where pp4=t.pp4
and pp0<t.pp0)
then 1
else 0
end
)
+
(
case when not exists(select 1
from cp
where pp5=t.pp5
and pp0<t.pp0)
then 1
else 0
end
)
from cp t
where (select count(1) from cp where pp0<t.pp0)>=8
--那么可以使用下面语句.
declare @cp table(pp0 int,pp1 int,pp2 int,pp3 int,pp4 int,pp5 int,number1 int)
insert into @cp(pp0,pp1,pp2,pp3,pp4,pp5)
select 1 , 5 , 11 ,18 ,19 ,22 union
select 2 , 4 , 6 ,12 ,21 ,22 union
select 3 , 12 , 14 ,15 ,16 ,21 union
select 4 , 4 , 6 ,7 ,15 ,18 union
select 5 , 1 , 2 ,6 ,8 ,9 union
select 6 , 4 , 6 ,8 ,10 ,13 union
select 7 , 10 , 14 ,16 ,17 ,18 union
select 8 , 1 , 6 ,9 ,10 ,20 union
select 9 , 3 , 6 ,12 ,14 ,15 union
select 10, 2 ,4 ,5 ,8 ,17 union
select 11, 4 , 8 ,17 ,20 ,21 union
select 12, 6 , 7 ,11 ,14 ,18 update a
set number1=case when (select count(*)+1 from @cp where pp0<a.pp0)<=8 then 0
else (case when (select count(*) from @cp where pp0<a.pp0 and (pp1=a.pp1 or pp2=a.pp1 or pp3=a.pp1 or pp4=a.pp1 or pp5=a.pp1))=0 then 1 else 0 end)+
(case when (select count(*) from @cp where pp0<a.pp0 and (pp1=a.pp2 or pp2=a.pp2 or pp3=a.pp2 or pp4=a.pp2 or pp5=a.pp2))=0 then 1 else 0 end)+
(case when (select count(*) from @cp where pp0<a.pp0 and (pp1=a.pp3 or pp2=a.pp3 or pp3=a.pp3 or pp4=a.pp3 or pp5=a.pp3))=0 then 1 else 0 end)+
(case when (select count(*) from @cp where pp0<a.pp0 and (pp1=a.pp4 or pp2=a.pp4 or pp3=a.pp4 or pp4=a.pp4 or pp5=a.pp4))=0 then 1 else 0 end)+
(case when (select count(*) from @cp where pp0<a.pp0 and (pp1=a.pp5 or pp2=a.pp5 or pp3=a.pp5 or pp4=a.pp5 or pp5=a.pp5))=0 then 1 else 0 end)
end
from @cp aselect * from @cp
/*
结果
1 5 11 18 19 22 0
2 4 6 12 21 22 0
3 12 14 15 16 21 0
4 4 6 7 15 18 0
5 1 2 6 8 9 0
6 4 6 8 10 13 0
7 10 14 16 17 18 0
8 1 6 9 10 20 0
9 3 6 12 14 15 1
10 2 4 5 8 17 0
11 4 8 17 20 21 0
12 6 7 11 14 18 0*/
每次取前8条比较:
declare @cp table(pp0 int,pp1 int,pp2 int,pp3 int,pp4 int,pp5 int,number1 int)
insert into @cp(pp0,pp1,pp2,pp3,pp4,pp5)
select 1 , 5 , 11 ,18 ,19 ,22 union
select 2 , 4 , 6 ,12 ,21 ,22 union
select 3 , 12 , 14 ,15 ,16 ,21 union
select 4 , 4 , 6 ,7 ,15 ,18 union
select 5 , 1 , 2 ,6 ,8 ,9 union
select 6 , 4 , 6 ,8 ,10 ,13 union
select 7 , 10 , 14 ,16 ,17 ,18 union
select 8 , 1 , 6 ,9 ,10 ,20 union
select 9 , 3 , 6 ,12 ,14 ,15 union
select 10, 2 ,4 ,5 ,8 ,17 union
select 11, 4 , 8 ,17 ,20 ,21 union
select 12, 6 , 7 ,11 ,14 ,18 update a
set number1=case when (select count(*)+1 from @cp where pp0<a.pp0)<=8 then 0
else (case when (select count(*) from (select top 8 * from @cp where pp0<a.pp0 order by pp0 desc) new1 where (pp1=a.pp1 or pp2=a.pp1 or pp3=a.pp1 or pp4=a.pp1 or pp5=a.pp1))=0 then 1 else 0 end)+
(case when (select count(*) from (select top 8 * from @cp where pp0<a.pp0 order by pp0 desc) new1 where (pp1=a.pp2 or pp2=a.pp2 or pp3=a.pp2 or pp4=a.pp2 or pp5=a.pp2))=0 then 1 else 0 end)+
(case when (select count(*) from (select top 8 * from @cp where pp0<a.pp0 order by pp0 desc) new1 where (pp1=a.pp3 or pp2=a.pp3 or pp3=a.pp3 or pp4=a.pp3 or pp5=a.pp3))=0 then 1 else 0 end)+
(case when (select count(*) from (select top 8 * from @cp where pp0<a.pp0 order by pp0 desc) new1 where (pp1=a.pp4 or pp2=a.pp4 or pp3=a.pp4 or pp4=a.pp4 or pp5=a.pp4))=0 then 1 else 0 end)+
(case when (select count(*) from (select top 8 * from @cp where pp0<a.pp0 order by pp0 desc) new1 where (pp1=a.pp5 or pp2=a.pp5 or pp3=a.pp5 or pp4=a.pp5 or pp5=a.pp5))=0 then 1 else 0 end)
end
from @cp aselect * from @cp
/*
结果
1 5 11 18 19 22 0
2 4 6 12 21 22 0
3 12 14 15 16 21 0
4 4 6 7 15 18 0
5 1 2 6 8 9 0
6 4 6 8 10 13 0
7 10 14 16 17 18 0
8 1 6 9 10 20 0
9 3 6 12 14 15 1
10 2 4 5 8 17 1
11 4 8 17 20 21 0
12 6 7 11 14 18 1
*/