declare @d float(4),@dt float(4)
declare @n int,@tt int,@avgt int
set @n=datediff(dd,'2005-1-1','2005-12-31')+1
set @tt=(1+@n)*@n/2
set @avgt=@tt/@n
select @d=avg(Dt) from sales_1
select @dt=Dt from sales_1
where 销售日期='2005-1-1'
update sales_1
set TD=(@dt-@d)*(@n-1-@avgt)
where 销售日期='2005-1-1'
这一段,我能在TD里的第一行得到我要的结果。我把这段复制进存储过程里,改了参数。
如下:
/*@firstday是'2005-1-1',@lastday'2005-12-31',是存储过程的两个输入参数*/
while @firstday<=@lastday
begin
/* set @n=datediff(dd,@firstday,@lastday)+1
set @tt=(1+@n)*@n/2
set @avgt=@tt/@n 这段我就不写了,存储过程一开始就写了这段,结果得到了*/
select @d=avg(Dt) from sales_1
select @dt=Dt from sales_1
where 销售日期=@firstday
update sales_1
set TD=(@dt-@d)*(@n-1-@avgt)
where 销售日期=@firstday
set @firstday=@firstday+1
set @n=@n-1
end
但是我执行存储过程,结果还是TD那列全是NULL,我试过几个方法改,没用,快郁闷死了。
declare @n int,@tt int,@avgt int
set @n=datediff(dd,'2005-1-1','2005-12-31')+1
set @tt=(1+@n)*@n/2
set @avgt=@tt/@n
select @d=avg(Dt) from sales_1
select @dt=Dt from sales_1
where 销售日期='2005-1-1'
update sales_1
set TD=(@dt-@d)*(@n-1-@avgt)
where 销售日期='2005-1-1'
这一段,我能在TD里的第一行得到我要的结果。我把这段复制进存储过程里,改了参数。
如下:
/*@firstday是'2005-1-1',@lastday'2005-12-31',是存储过程的两个输入参数*/
while @firstday<=@lastday
begin
/* set @n=datediff(dd,@firstday,@lastday)+1
set @tt=(1+@n)*@n/2
set @avgt=@tt/@n 这段我就不写了,存储过程一开始就写了这段,结果得到了*/
select @d=avg(Dt) from sales_1
select @dt=Dt from sales_1
where 销售日期=@firstday
update sales_1
set TD=(@dt-@d)*(@n-1-@avgt)
where 销售日期=@firstday
set @firstday=@firstday+1
set @n=@n-1
end
但是我执行存储过程,结果还是TD那列全是NULL,我试过几个方法改,没用,快郁闷死了。
update sales_1
set TD=(isnull(@dt,0)-isnull(@d,0))*(isnull(@n,0)-1-isnull(@avgt,0))
where 销售日期=@firstday
update sales_1 set TD=(isnull(@dt,0)-(isnull(@d,0))*((isnull(@n,0)-1-(isnull(@avgt,0))
where 销售日期='2005-1-1'