22,206
社区成员
发帖
与我相关
我的任务
分享
ALTER FUNCTION [dbo].[fn_GetAn](@n INT)
RETURNS DECIMAL(19,8)
AS
BEGIN
DECLARE @d DECIMAL(19,8)
/*
IF @n=1 RETURN 0.5
ELSE SET @d=dbo.fn_GetAn(@n-1)
RETURN @d*@d/2016+@d
*/
;WITH cte AS (
SELECT CONVERT(DECIMAL(19,8),0.5) AS r,1 AS l
UNION ALL
SELECT CONVERT(DECIMAL(19,8),r*r/2016+r),l+1 AS l FROM cte WHERE l<@n
)
SELECT @d=r FROM cte WHERE l=@n
OPTION (MAXRECURSION 0)
RETURN @d
END
SELECT sv.number, dbo.fn_GetAn(sv.number) FROM master.dbo.spt_values AS sv WHERE sv.type='P' AND sv.number BETWEEN 999 AND 1000
number (No column name)
999 0.66440698
1000 0.66462595
CREATE FUNCTION [dbo].[fn_GetAn](@n INT)
RETURNS DECIMAL(19,8)
AS
BEGIN
DECLARE @d DECIMAL(19,8)
IF @n=1 RETURN 0.5
ELSE SET @d=dbo.fn_GetAn(@n-1)
RETURN @d*@d/2016+@d
END
调用示例,你看对不对:
SELECT sv.number, dbo.fn_GetAn(sv.number) FROM master.dbo.spt_values AS sv WHERE sv.type='P' AND sv.number BETWEEN 1 AND 20
number (No column name)
1 0.50000000
2 0.50012401
3 0.50024808
4 0.50037221
5 0.50049640
6 0.50062065
7 0.50074497
8 0.50086935
9 0.50099379
10 0.50111829
11 0.50124285
12 0.50136748
13 0.50149217
14 0.50161692
15 0.50174173
16 0.50186660
17 0.50199154
18 0.50211654
19 0.50224160
20 0.50236672
上面是最基本的写法,有个相纸,最多递归32层WITH t1(id) as
(
select number+1 id from master..spt_values where type='P' and number<20
)
,t2(id,Result, level) as
(
SELECT id, convert(float, 0.5*0.5/2016.0+0.5), 1 FROM t1 WHERE id=1
UNION ALL
SELECT a.id, convert(float, b.Result*b.Result/2016.0+b.Result), level+1 FROM t1 a,t2 b
where a.id = b.level
)
SELECT * from t2
--递归10次
declare @i int=10
;with cte2 as
(
select num = convert(float,0.5),1 as [level]
union all
select (c2.num*c2.num/2016.0+c2.num),c2.[level]+1 from cte2 c2 where c2.level<@i
)select * from cte2
declare @i int=10
;with cte1(id,num) as
(
select 1,convert(float,0.5)
),cte2 as
(
select num,1 as [level] from cte1 c1 where c1.id=1
union all
select (c2.num*c2.num/2016.0+c2.num),c2.[level]+1 from cte2 c2 where c2.level<@i
)select * from cte2
--递归10次
declare @i int=10
;with cte1(id,num) as
(
select 1,convert(float,0.5*0.5/2016+0.5)
),cte2 as
(
select (num*num/2016.0+num) as num,1 as [level] from cte1 c1 where c1.id=1
union all
select (c2.num*c2.num/2016.0+c2.num),c2.[level]+1 from cte2 c2 where c2.level<@i
)select * from cte2