用SQL实现阶乘

[qq_44793651]

用SQL实现阶乘，哪位大佬，搞不出来

[doloopcn]

怎么会呢，在SQL SERVER中，写一个自定义函数不就行了

[BlueStorm]

CREATE FUNCTION factorial(@num INTEGER) RETURNS BIGINT
AS
BEGIN
  DECLARE @I INTEGER=1, @result INTEGER = 1;
  WHILE @I <= @num
  BEGIN
    SET @result  = @result  * @I;
    SET @I = @I + 1;
  END;
  RETURN @result;
END;
GO 

SELECT dbo.factorial(10)
GO

--------------------
3628800

(1 行受影响)

[BlueStorm]

DECLARE @num INT = 10;
WITH cte AS
(
  SELECT I = 1, result = 1
  UNION ALL
  SELECT I = I + 1, result = result * I FROM cte WHERE I <= @num-1
)
SELECT TOP 1 result FROM cte ORDER BY I DESC

result
-----------
362880

(1 行受影响)

[BlueStorm]

WITH cte AS
(
  SELECT I = 1, result = 1
  UNION ALL
  SELECT I = I + 1, result = result * I FROM cte WHERE I <= 10
)
SELECT TOP 1 result FROM cte ORDER BY I DESC;
GO

[写代码也要符合基本法]

法1：利用递归公共表表达式

WITH factorial(n, f) AS
 (SELECT 1 n, 1 f
    FROM dual
  UNION ALL
  SELECT n + 1, f * (n + 1)
    FROM factorial
   WHERE n < 10)
SELECT MAX(f) f FROM factorial;

法2：利用MODEL的迭代器

WITH factorial AS
 (SELECT n, f
  FROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL <= 10) 
  MODEL RETURN UPDATED ROWS 
  DIMENSION BY(n) MEASURES(0 f) 
  RULES ITERATE(10)
  (f[n] ORDER BY n = presentv(f[cv(n) - 1], f [cv(n) - 1], 1) * cv(n)))
SELECT MAX(f) f FROM factorial;

法3：参考数学公式lg(MN)=lg(M)+lg(N)

SELECT power(10, SUM(log(10, LEVEL))) f FROM dual CONNECT BY LEVEL <= 10;