在 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屋！