修改现有的SQL语句，增加一列数据，但不影响性能，这是最好的方法

Question

在这个查询中，我想添加一个新列，它给出a.v olvolumetriccharge的SUM，但只在PremiseProviderBillings中。BillingCategory = 'Water'。但我不想在明显的地方添加它，因为这会限制返回的行，我只希望它获得新的列值

SELECT b.customerbillid,
-- Here i need  SUM(a.VolumetricCharge) but where a.BillingCategory is equal to 'Water'
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
-- Cannot add a where here since that would limit the results and change the output
GROUP  BY b.customerbillid;

Answer 1

有点棘手，因为你所要求的肯定会影响性能(毕竟你要求SQL Server做更多的工作!)

但是，我们可以在结果中添加一个列，该列执行条件和，以便它不影响其他列的结果。

答案在于使用CASE表达式!

Sum(
CASE
WHEN PremiseProviderBillings.BillingCategory = 'Water' THEN
a.volumetriccharge
ELSE
0
END
) AS WaterVolumetric