Sql Server 指定列的乘积

最新推荐文章于 2024-06-26 17:54:02 发布

原创最新推荐文章于 2024-06-26 17:54:02 发布 · 6.1k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #函数

MS-SQL 专栏收录该内容

30 篇文章

订阅专栏

本文介绍了一种在SQL Server中统计指定列乘积的方法，利用EXP(SUM(LOG(字段)))函数来实现，同时解决了当字段中包含0值时的处理方案。

需要统计指定列的乘积
SQL SERVER中有EXP(SUM(LOG(字段)))的函数
如下例子：

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

EXP(SUM(LOG(字段))) 字段的值是不能够包含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