Sql Server 指定列的乘积

最新推荐文章于 2023-12-21 18:35:23 发布

waterstar50

最新推荐文章于 2023-12-21 18:35:23 发布

阅读量5.8k

点赞数 1

分类专栏： MS-SQL 文章标签： sql server 函数

本文链接：https://blog.csdn.net/waterstar50/article/details/78495905

版权

MS-SQL 专栏收录该内容

30 篇文章 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