TSQL的连乘

某个需求需要对某一列的值做乘法，网上搜了把确实还真没有直接的聚合函数用于将某一列的值乘起来。

找到了替代的算法：

http://jerryyang-wxy.blogspot.com/2012/04/transact-sql.html

http://blog.csdn.net/walkbob/article/details/45508501

------------------------------------------------------------------------------------------------

新问题出现了：如果要做连乘的那一列出现了0的值或者是小于0的值，做LOG运算时会出现

An invalid floating point Operation occurred.的错误

下面这个例子用于解决某列的值有小于等于零的值而又要做连乘的TSQL：

--生成测试表DROP TABLE #TSELECT 1 AS A INTO #TUNION ALL SELECT 2UNION ALL SELECT 3UNION ALL SELECT 4UNION ALL SELECT 0UNION ALL SELECT -1UNION ALL SELECT -2UNION ALL SELECT -3UNION ALL SELECT -4UNION ALL SELECT -5--定义三个参数，用于分别计算大于0的值，等于0的值，小于0的值的连乘结果declare @oZero as decimal(12,0)declare @eZero as decimal(12,0)declare @lZero as decimal(12,0)--大于0的值select @oZero = ISNULL(POWER(10.0,SUM(LOG(A))),1)FROM #TWHERE A > 0--等于0的值（相加即可）select @eZero = isnull(sum(A),1)FROM #TWHERE A = 0--小于0的值select @lZero = ISNULL(POWER(10.0,SUM(LOG10(A*-1))),1)*power(-1,(count(1)))FROM #TWHERE A < 0--三个结果相乘select @oZero * @eZero * @lZero as result