SQL Server 斐波那契数列

斐波那契数列，又称黄金分割数列，指的是这样一个数列：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
*/