如何将列中的所有值与 SUM() 等 SQL 相乘 [英] How to Multiply all values within a column with SQL like SUM()

问题描述

假设我有这样的 1 列表格:

Lets say I have table with 1 column like this:

Col A
1
2
3
4

如果我 SUM 它，那么我会得到这个:

If I SUM it, then I will get this:

Col A
10

我的问题是:如何将 Col A 相乘才能得到以下结果?

My question is: how do I multiply Col A so I get the following?

Col A
24

推荐答案

结合使用ROUND、EXP、SUM和日志

SELECT ROUND(EXP(SUM(LOG([Col A]))),1)
FROM yourtable

SQL 小提琴:http://sqlfiddle.com/#!3/d43c8/2/0

解释

LOG 返回 col a ex 的对数.LOG([Col A]) 返回

LOG returns the logarithm of col a ex. LOG([Col A]) which returns

0
0.6931471805599453
1.0986122886681098
1.3862943611198906

然后您使用 SUM 将它们全部加在一起 ​​SUM(LOG([Col A])) 返回

Then you use SUM to Add them all together SUM(LOG([Col A])) which returns

3.1780538303479453

然后使用返回的 EXP(SUM(LOG(['3.1780538303479453']))) 计算该结果的指数

Then the exponential of that result is calculated using EXP(SUM(LOG(['3.1780538303479453']))) which returns

23.999999999999993

然后最后使用 ROUND ROUND(EXP(SUM(LOG('23.9999999999999993'))),1) 进行四舍五入得到 24

Then this is finally rounded using ROUND ROUND(EXP(SUM(LOG('23.999999999999993'))),1) to get 24

简单的解决方法:

发生了无效的浮点运算.

An invalid floating point operation occurred.

当您的数据中有 0 时

SELECT ROUND(EXP(SUM(LOG([Col A]))),1)
FROM yourtable
WHERE [Col A] != 0

如果你只有 0 那么上面会给出 NULL 的结果.

If you only have 0 Then the above would give a result of NULL.

当您的数据集中有负数时.

When you have negative numbers in your data set.

SELECT (ROUND(exp(SUM(log(CASE WHEN[Col A]<0 THEN [Col A]*-1 ELSE [Col A] END))),1)) * 
(CASE (SUM(CASE WHEN [Col A] < 0 THEN 1 ELSE 0 END) %2) WHEN 1 THEN -1 WHEN 0 THEN 1 END) AS [Col A Multi]
FROM yourtable

示例输入:

1
2
3
-4

输出:

Col A Multi
-24

SQL 小提琴:http://sqlfiddle.com/#!3/01ddc/3/0

这篇关于如何将列中的所有值与 SUM() 等 SQL 相乘的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！