写错了!!!问题:主要是统计号码之和,对比号为本条记录前8条记录的号码。如第9条记录的number1写入前8条记录(第1条至第8条)pp1之和41,即(5+4+12+4+1+4+10+1)=41;在第10条记录的number1写入前8条记录(第2条至第9条)pp1之和39,即(4+12+4+1+4+10+1+3)=39,依次类推第11条记录的number1应写入37,即(12+4+1+4+10+1+3+2)=37
(前8条记录的number1没有对比号,写入0)
要求:用少量sql语句实现,越少越好,最好一条,不能用游标。
(前8条记录的number1没有对比号,写入0)
要求:用少量sql语句实现,越少越好,最好一条,不能用游标。
update t
set number1=(select sum(pp1) from cp where t.pp0-pp0<=8)
from cp t
where pp0>8
update t
set number1=(select sum(pp1) from cp where t.pp0-pp0<=8)
from cp t
where pp0>8update cp set number1=0 where pp0<=8
update t
set number1=(select sum(pp1)
from cp
where pp0 in (select top 8 pp0
from cp
where pp0<t.pp0
order by pp0 desc))
from cp t
where (select count(1) from cp where pp0<t.pp0)>=8update cp set number1=0 where pp0<=8
update t
set number1=case when pp0<=8 then 0 else (select sum(pp1) from cp where pp0+8<=t.pp0) end
from cp t
where pp0>8
update a
set number1=b.number1
from cp a,
(select a.pp0, number1=case when count(b.pp0)<=8 then 0
else sum(b.pp0) end
from cp a,cp b
where b.ppo+8<=a.ppo group by a.ppo) b
where a.pp0=b.pp0