如何修改现有SQL语句新增指定条件求和列且不影响原有结果与性能

核心实现逻辑 #

使用条件聚合实现，将判断逻辑放在聚合函数内部，不会改变原有查询的返回范围，也没有额外性能损耗。
该方案优势：

• 不需要修改WHERE、JOIN、GROUP BY逻辑，原有查询的返回行数、其他列计算结果完全不变
• 仅在单次表扫描的聚合阶段做条件判断，和原查询性能几乎一致，无额外IO开销
• 语法兼容所有主流关系型数据库

修改后的完整SQL #

SELECT b.customerbillid,
       -- 新增的Water类别 volumetric charge 求和列
       SUM(CASE WHEN a.BillingCategory = 'Water' THEN a.VolumetricCharge ELSE NULL END) AS WaterVolumetric,
       Sum(a.volumetriccharge)                AS Volumetric,
       Sum(a.fixedcharge)                     AS Fixed,
       Sum(a.vat)                             AS VAT,
       Sum(a.discount) + Sum(deferral)        AS Discount,
       Sum(Isnull(a.estimatedconsumption, 0)) AS Consumption,
       Count_big(*)                           AS Records
FROM   dbo.premiseproviderbillings AS a WITH (nolock)
       LEFT JOIN dbo.premiseproviderbills AS b WITH (nolock)
              ON a.premiseproviderbillid = b.premiseproviderbillid
GROUP  BY b.customerbillid;

补充说明 #

如果要求没有Water类别的记录返回0而不是NULL，只需要把CASE里的ELSE NULL改成ELSE 0即可。

内容的提问来源于stack exchange，提问作者John