SQL 函数 - 阶乘
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3504932/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Functions - factorial
提问by Jason
I am a beginner in SQL Functions. What is the best way to create a function for factorial in SQL Server- Say 10!
我是 SQL 函数的初学者。在 SQL Server 中为阶乘创建函数的最佳方法是什么 - 说 10！
回答by Martin Smith
A non recursive way
非递归方式
;With Nums As
(
select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RN
FROM sys.objects
)
SELECT POWER(10.0, SUM(LOG10(RN)))
FROM Nums
WHERE RN <= 10
And a recursive way
和递归方式
declare @target int
set @target=10;
WITH N AS
(SELECT 1 AS i,
1 AS f
UNION ALL
SELECT i+1,
f*(i+1)
FROM N
WHERE i < @target
)
SELECT f FROM N
WHERE i=@target
回答by Abe Miessler
Here is a recursive solution:
这是一个递归解决方案：
CREATE FUNCTION dbo.Factorial ( @iNumber int )
RETURNS INT
AS
BEGIN
DECLARE @i int
IF @iNumber <= 1
SET @i = 1
ELSE
SET @i = @iNumber * dbo.Factorial( @iNumber - 1 )
RETURN (@i)
END
回答by Ben
-- Iterative method. -- Why Iterative? It is simpler and faster. -- For @N from 0 to 20 this gives an exact result. -- 21 will give an overflow.
——迭代法。-- 为什么要迭代？它更简单、更快。-- 对于从 0 到 20 的@N，这给出了准确的结果。-- 21 会溢出。
DECLARE @N Bigint = 20
DECLARE @F Bigint = 1
WHILE @N > 0 BEGIN
SET @F = @f*@n
SET @N = @N-1
END
SELECT @F AS FACTORIAL
-- Change the datatype to float and you can get the factorial up to 170. -- 171 will result in an overflow. -- Remark the result will only be accurate over a limited number of positions.
-- 将数据类型更改为浮点数，您可以获得高达 170 的阶乘。 -- 171 将导致溢出。-- 请注意，结果只会在有限数量的位置上准确。
DECLARE @N FLOAT = 170
DECLARE @F FLOAT = 1
WHILE @N > 0 BEGIN
SET @F = @f*@n
SET @N = @N-1
END
SELECT @F AS FACTORIAL
-- Ben
——本
回答by Nargis Shaikh
Try this
尝试这个
WITH MYCTE AS(
SELECT VAL=1,NUM =6
UNION ALL
SELECT VAL=VAL*NUM,NUM = (NUM -1)
FROM MYCTE
WHERE NUM > 1
)
SELECT VAL FROM MYCTE
回答by Factor
Another way:
其它的办法：
create function Fact(@num int)
returns bigint
as
begin
declare @i int = 1
while @num>1
begin
set @i = @num * @i
set @num=@num-1
end
return @i
end
select dbo.Fact(5)
回答by Alan Burstein
... for my Set-based method:
...对于我的基于 Set 的方法：
DECLARE @n int=11, @f bigint=1;
WITH
t(n,f) AS (SELECT TOP(@n)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))+1,
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) *
(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))+1)
FROM sys.all_columns
UNION SELECT 1, f=CASE WHEN @n=0 THEN 0 ELSE 1 END)
SELECT @f=@f*f
FROM t
WHERE n%2=@n%2 OR f=0;
SELECT @f AS FACTORIAL;
回答by Khadeer Ali
/* Print Factorial Sequence*/
/* 打印阶乘序列*/
WITH MYCTE AS(
SELECT VAL=1,NUM =1
UNION ALL
SELECT VAL=VAL*(NUM+1),NUM = (NUM +1)
FROM MYCTE
WHERE NUM < 11
)
SELECT VAL FROM MYCTE
OPTION (MAXRECURSION 0);
回答by Eralper
Here is an other method to calculate factorial value of an integer in SQL Server
这是在 SQL Server 中计算整数阶乘值的另一种方法
create function sqlFactorial (@int int)
returns int
begin
declare @factorial bigint = 1
select @factorial = @factorial * i from dbo.NumbersTable(1,@int,1)
return @factorial
end
You need to use a SQL numbers tablefor this solution. The Select statement updates the declared integer variable for each row in the FROM part with multiplying it with the ordered integer values
您需要为此解决方案使用SQL 数字表。Select 语句更新 FROM 部分中每一行的声明整数变量，并将其与有序整数值相乘
回答by John Wright
If you are okay with an approximation, use Stirling's Approximation.
如果您可以使用近似值，请使用斯特林近似值。
create table #temp (value int)
insert into #temp values (5),(6),(7),(8)
select
value,
sqrt(2*3.14*value)*power((value/2.718),value) --stirling's approx.
from #temp
Note that you will have to make a case for 0!, if needed.
请注意，如果需要，您必须为 0！
回答by Dojo
You asked which is the bestway to create a function for factorial in SQL Server. As always, that depends on the context. But if you truly mean it in the generic sense, where performance matters, the best way to go is without a doubt to implement it as a CLR user-defined function.
您询问哪种方法是在 SQL Server 中为阶乘创建函数的最佳方法。与往常一样，这取决于上下文。但是，如果您真正指的是一般意义上的性能问题，那么最好的方法无疑是将其实现为 CLR 用户定义函数。
You can of course implement the function itself in whatever language you fancy. And a long/bigint doesn't really cut it for a factorial function (a bigint can only fit up to 20!, 21! is arithmetic overflow).
你当然可以用你喜欢的任何语言来实现函数本身。对于阶乘函数，long/bigint 并没有真正削减它（bigint 最多只能容纳 20！，21！是算术溢出）。
