CodePudding user response:
How can, in SQL SERVER, to write a custom function not line
CodePudding user response:
The CREATE FUNCTION factorial (@ num INTEGER) RETURNS BIGINT
AS
The BEGIN
DECLARE @ INTEGER I=1, @ result INTEGER=1;
WHILE @ I & lt;=@ num
The BEGIN
The SET @ result=@ result * @ I;
The SET @ I=@ I + 1;
END;
RETURN @ the result;
END;
GO
The SELECT dbo. Factorial (10)
GO
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
3628800
Line (1) affected
CodePudding user response:
DECLARE @ num INT=10;
WITH cte AS
(
SELECT I=1, the result=1
UNION ALL
SELECT I=I + 1, the result=result * I FROM cte WHERE I & lt;=@ num 1
)
SELECT the TOP 1 result FROM cte ORDER BY DESC I
The result
-- -- -- -- -- -- -- -- -- -- --
362880
Line (1) affected
CodePudding user response:
WITH cte AS
(
SELECT I=1, the result=1
UNION ALL
SELECT I=I + 1, the result=result * I FROM cte WHERE I & lt;=10
)
SELECT the TOP 1 result FROM cte ORDER BY DESC I;
GO
CodePudding user response:
Method 1: using a recursive common table expressions
WITH the factorial (n, f) AS
(SELECT 1 n, 1 f
The FROM dual
UNION ALL
SELECT the n + 1, f * (n + 1)
The FROM the factorial
WHERE n & lt; 10)
SELECT MAX (f) f the FROM factorial.
Method 2: using the MODEL of the iterator
WITH the factorial AS
(SELECT n, f
The FROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL & lt;=10)
The MODEL RETURN UPDATED ROWS
DIMENSION BY (n) MEASURES (0 f)
RULES ITERATE (10)
[n] ORDER BY n=presentv (f (f/CV (n) - 1, f/CV (n) 1, 1) * CV (n)))
SELECT MAX (f) f the FROM factorial.
Method 3: refer to the mathematical formula of lg (MN)=lg (M) + lg (N)
SELECT the power (10, the SUM (log (10, LEVEL))) f the FROM dual CONNECT BY LEVEL & lt;=10;