将 t-sql 中的行相乘

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

我有下表

 ID     Number    
----------------     
   1       41.5
   2       42.5
   3       43.5
   2       44.5   
   2       45.5
   1       46.5
   1       47.5

我需要编写一个查询，该查询将返回不同的 ID 和相应的 Number 列值相乘.对于给定的表结果应该是这样的

I need to write a query which will return distinct ID's and corresponding Number column values multiplied. For the given table result should be like this

ID       Result 
-----------------
  1         41.5 * 46.5 * 47.5
  2         42.5 * 44.5 * 45.5
  3         etc...

(不使用游标)

推荐答案

SELECT Id, EXP(SUM(LOG(Number))) as Result
FROM Scores 
GROUP BY id

这适用于正数，也可以乘以负数，您可以使用 ABS() 函数使用绝对(正)值，但最终结果将是正数而不是负数:

This will work for positive numbers, to multiply negative numbers as well you can use ABS() function to use absolute (positive) value but final result will be positive rather than negative number:

SELECT Id, EXP(SUM(LOG(ABS(Number)))) as Result
FROM Scores 
GROUP BY id

<小时>

添加了测试脚本

DECLARE @data TABLE(id int, number float)

INSERT INTO @data VALUES
(1, 2.2),
(1, 10),
(2, -5.5),
(2, 10)

SELECT Id, EXP(SUM(LOG(ABS(Number)))) as Result 
FROM @data GROUP BY id

输出:

1   22
2   55

相关文章