因为你有个@day=@day+1,所以到了第60笔时,@day应该=61
try:
declare @day int ,@acurd numeric(10,3),@avga numeric(10,3)
set @day=0
update workers set @day=@day+1,
@acurd=case
when @day=61 then (select avg(isnull(b.sailer,0)) from (select top 60 sailer from workers
where num='20000001' order by updtime asc) as b)
when @day>61 then (isnull(sailer,0)-isnull(@acurd,0))*0.15+isnull(@acurd,0)
else 0 end ,SAI=@acurd
where num='20000001'
try:
declare @day int ,@acurd numeric(10,3),@avga numeric(10,3)
set @day=0
update workers set @day=@day+1,
@acurd=case
when @day=61 then (select avg(isnull(b.sailer,0)) from (select top 60 sailer from workers
where num='20000001' order by updtime asc) as b)
when @day>61 then (isnull(sailer,0)-isnull(@acurd,0))*0.15+isnull(@acurd,0)
else 0 end ,SAI=@acurd
where num='20000001'
你的也不对呀,这样第61条就是NULL国
declare @day int ,@acurd numeric(10,3),@avga numeric(10,3),@te numeric(10,3)
set @day=0
select @te=avg(isnull(b.sailer,0)) from (select top 60 sailer from workers where num='20000001' order by updtime asc) as b)
update workers set @day=@day+1,
@acurd=case
when @day=60 then @te
when @day>60 then (isnull(sailer,0)-isnull(@acurd,0))*0.15+isnull(@acurd,0)
else 0 end ,SAI=@acurd
where num='20000001'
不对啊。
set @day=0
while @day<60
begin
update workers set @day=@day+1
if @day=60 break
endupdate workers set @day=@day,@acurd=case
when @day=60 then (select avg(isnull(b.sailer,0)) from (select top 60 sailer from workers where num='20000001' order by updtime asc) as b)
when @day>60 then (isnull(sailer,0)-isnull(@acurd,0))*0.15+isnull(@acurd,0)
else 0 end ,SAI=@acurd
where num='20000001'