斐波那契数列，又称黄金分割数列，指的是这样一个数列：0、1、1、2、3、5、8、13、21、……在数学上，斐波纳契数列以如下被以递归的方法定义：F0=0，F1=1，Fn=F(n-1)+F(n-2)（n>=2，n∈N*）在现代物理、准晶体结构、化学等领域，斐波纳契数列都有直接的应用，为此，美国数学会从1960年代起出版了《斐波纳契数列》季刊，专门刊载这方面的研究成果。（更多资料：斐波那契数列）
--斐波那契数列(0,1,1,2,5,8,13,21,34,55,89……)
--数据库类型长度有限，最大输出到94个。嵌套查询只32个。
--【1. 第N个斐波那契数列】
CREATE FUNCTION dbo.fib(@n INT)
RETURNS INT
AS
BEGIN
DECLARE @return INT
IF @n = 0
SELECT @return = 0
ELSE IF @n = 1
SELECT @return = 1
ELSE IF @n > 1
SELECT @return = (dbo.fib(@n-2)+dbo.fib(@n-1))
RETURN @return;
END
PRINT'超出了存储过程、函数、触发器或视图的最大嵌套层数(最大层数为 32)'
SELECT dbo.fib(32) AS fib
/*
fib
---
2178309
*/
--【2. 前N个斐波那契数列】
CREATE FUNCTION dbo.fib_col(@n INT) --n:1~92
RETURNS @result TABLE(fib BIGINT)
--decimal(37,0):10^38 - 1,可显示94个
AS
BEGIN
DECLARE @k INT=1
DECLARE @a BIGINT=0
DECLARE @b BIGINT=1
DECLARE @fib BIGINT=0
IF @n = 1
BEGIN
INSERT INTO @result SELECT 0
SET @k = @k + 1
END
ELSE IF @n >= 2 AND @n <= 92
BEGIN
INSERT INTO @result VALUES(0),(1)
SET @k = @k + 1
END
ELSE RETURN
--bigint范围: 2^63-1 (9,223,372,036,854,775,807)
WHILE @fib < 9223372036854775807
BEGIN
SET @k = @k + 1
SET @fib = @a + @b
SET @a = @b
SET @b = @fib
IF @k > @n RETURN
INSERT INTO @result SELECT @fib
END
RETURN
END
SELECT * FROM dbo.fib_col(10)
/*
fib
---
0
1
1
2
3
5
8
13
21
34
*/
--【3. 前N个斐波那契数列】
CREATE FUNCTION dbo.fib_row(@n INT) --n:1~92
RETURNS varchar(max)
AS
BEGIN
DECLARE @k INT=1
DECLARE @a BIGINT=0
DECLARE @b BIGINT=1
DECLARE @fib BIGINT=0
DECLARE @result varchar(max)=''
IF @n = 1
BEGIN
SET @result = ISNULL(@result,'')+'0'
SET @k = @k + 1
END
ELSE IF @n >= 2 AND @n <= 92
BEGIN
SET @result = ISNULL(@result,'')+'0,1'
SET @k = @k + 1
END
ELSE RETURN @result
--bigint范围: 2^63-1 (9,223,372,036,854,775,807)
WHILE @fib < 9223372036854775807
BEGIN
SET @k = @k + 1
SET @fib = @a + @b
SET @a = @b
SET @b = @fib
IF @k > @n RETURN @result
SET @result = @result+','+CONVERT(VARCHAR(50),@fib)
END
RETURN @result
END
SELECT dbo.fib_row(20) AS fib
/*
fib
---------------------------------------------------------------
0,1,1,2,3,5,8,13,21,34,55,89,144,233,377,610,987,1597,2584,4181
*/
--【4. 所有可输出的斐波那契数列】
;WITH Fib(a,b)
AS (
SELECT a,b FROM (VALUES(CONVERT(BIGINT,0),CONVERT(BIGINT,1))) AS T(a,b)
UNION ALL
SELECT b,a + b FROM Fib WHERE a < 9223372036854775807/2
) SELECT a FROM FIB
/*
a
---
0
1
1
2
……
……
2880067194370816120
4660046610375530309
*/