27,579
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('tb') is not null
drop table tb;
go
create table tb(dt datetime,sales numeric(10,2));
go
insert into tb
select '2010-08-01',5 union all
select '2010-08-02',8 union all
select '2010-08-03',10;
go
-- 方法 1:递归函数
-- 缺点:受递归层级影响,只能计算 32 天内的日均销量
if OBJECT_ID('ufn_avg_rec') is not null
drop function dbo.ufn_avg_rec;
go
create function dbo.ufn_avg_rec (@date datetime)
returns numeric(10,2) as
begin
declare @avg numeric(10,2);
if @date=(select MIN(dt) from tb)
select @avg=sales from tb where dt=@date;
else
select @avg=dbo.ufn_avg_rec(@date-1)*0.7+sales*0.3 from tb where dt=@date;
return @avg;
end
go
-- 方法 2:游标循环
-- 可以计算超过 32 天的日均销量
if OBJECT_ID('ufn_avg_loop') is not null
drop function dbo.ufn_avg_loop;
go
create function dbo.ufn_avg_loop (@date datetime)
returns numeric(10,2) as
begin
declare @avg numeric(10,2),@sales numeric(10,2);
declare c cursor local for
select sales from tb where dt<=@date order by dt;
open c;
fetch next from c into @avg;
while 1=1
begin
fetch next from c into @sales;
if @@FETCH_STATUS<>0 break;
set @avg=@avg*0.7+@sales*0.3;
end
close c;
return @avg;
end
go
select dbo.ufn_avg_loop('2010-08-03'),dbo.ufn_avg_rec('2010-08-03');
--sql 2000 函数递归算法2
if object_id('f_test') is not null drop function f_test
go
create function f_test(@dt int)
returns numeric(10,4)
as
begin
declare @i numeric(10,4)
select @i=number+isnull(dbo.f_test(number),0)*0.7 from master..spt_values where type='p' and number=@dt-1
return @i
end
go
select number+dbo.f_test(number)*0.7 from master..spt_values where type='p' and number=3
---select (2+0.7)*0.7+3
/*
---------------------------------------
4.89000
(1 行受影响)
*/
--sql 2000 函数递归算法
if object_id('f_test') is not null drop function f_test
go
create function f_test(@dt int)
returns int
as
begin
declare @i int
select @i=@dt+isnull(dbo.f_test(number),0) from master..spt_values where type='p' and number=@dt-1
return @i
end
go
select dbo.f_test(10)
/*
-----------
55
(1 行受影响)
*/
declare @date datetime
select sum(case when datediff(day,sale_date,@date)=2 then sale_qty else 0 end)* 0.49
+sum(case when datediff(day,sale_date,@date)=1 then sale_qty else 0 end)* 0.21
+sum(case when datediff(day,sale_date,@date)=0 then sale_qty else 0 end)* 0.3
from tb