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;
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
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 行受影响)
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 行受影响)