用SQL實作階乘，哪位大佬，搞不出來
uj5u.com熱心網友回復：
怎么會呢，在SQL SERVER中，寫一個自定義函式不就行了
uj5u.com熱心網友回復：
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 行受影響)
uj5u.com熱心網友回復：
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 行受影響)
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
法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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/14343.html
標籤：數據庫相關