在 SQL SERVER 中执行乘法(SET BASED APPROACH) [英] Performing multiplication in SQL SERVER(SET BASED APPROACH)
问题描述
假设我有一个如下所示的表格:
Suppose I have a table like the following:
Numbers
4
5
3
6
使用 SET BASED 方法如何执行乘法,以便输出:
Using SET BASED approach how can I perform a multiplication so the output will be:
Output
360
注意~没有硬性规定只有四个数字,但我更喜欢使用 CTE 和/或相关子查询的答案.
N.B~ There is no hard and fast rule that there will be only four numbers, but I'd prefer the answer to be using a CTE and/or correlated subquery.
推荐答案
您可以使用对数/指数来利用以下数学事实:
You can use logarithms/exponents that take advantage of the mathematical fact that:
log(a*b*c...*n)=log(a)+log(b)+log(c)...+log(n)
因此,您可以使用 sum
函数将列的所有对数相加,然后取该总和的指数,得出该列的聚合乘法:
Therefore you can use the sum
function to add all the logarithms of a column, then take the exponent of that sum, which gives the aggregate multiplication of that column:
create table #tbl (val int)
insert into #tbl (val) values(1)
insert into #tbl (val) values(2)
insert into #tbl (val) values(3)
insert into #tbl (val) values(4)
select exp(sum(log(val))) from #tbl
drop table #tbl
如果我没记错的话,有一个边缘情况需要注意...... log(0) 是一个错误.
If memory serves me right, there an edge case that needs to be taken care of... log(0) is an error.
这篇关于在 SQL SERVER 中执行乘法(SET BASED APPROACH)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!