sql server 累乘积

SQL Server中可以使用EXP(SUM(LOG(字段)))函数来实现累乘积的计算。例如，可以使用以下语句来统计指定列的乘积：

SELECT WorkOrder, LineId, ROUND(EXP(SUM(LOG(ThroughRate))), 4) * 100 AS ThroughRate
FROM #ThroughRate
GROUP BY WorkOrder, LineId

需要注意的是，字段的值不能包含0，否则会出现无效浮点数操作的错误。如果指定列含有0的话，最终的乘积是0。因此，可以考虑将结果集分成两部分，等于0和不等于0两种情况计算，最终在合并查询处理。具体实现可以参考以下语句：

SELECT m.WorkOrder, m.LineId,
CASE WHEN n.ThroughRate = 0 THEN 0 ELSE CONVERT(DECIMAL(18, 4), m.ThroughRate) END AS ThroughRate
FROM (
SELECT WorkOrder, LineId, ROUND(EXP(SUM(LOG(ThroughRate))), 4) * 100 AS ThroughRate
FROM #ThroughRate
WHERE ThroughRate != 0
GROUP BY WorkOrder, LineId
) m
LEFT JOIN (
SELECT WorkOrder, LineId, ThroughRate
FROM #ThroughRate
WHERE ThroughRate = 0
GROUP BY WorkOrder, LineId, ThroughRate
) n ON m.WorkOrder = n.WorkOrder AND m.LineId = n.LineId