sql - 具有CASE条件和SUM()的SELECT查询

Question

我目前正在使用这些sql语句。我的表的字段CPaymentType包含“现金”或“支票”。我可以通过执行2条SQL语句来汇总付款金额，如下所示。在这种情况下，用户甚至不会在执行2条sql语句或仅执行1条sql语句时注意到速度差异，但是，我不喜欢我的方式，我只想要1条sql语句。如何在CASE条件下将它们重构为1条语句？我无法弄清楚，因为在线示例结果为1或0或 bool(boolean) 值。我不希望包含过期的支票付款。非常感谢你。

Select SUM(CAmount) as PaymentAmount 
from TableOrderPayment 
where CPaymentType='Cash' and CStatus='Active';

Select SUM(CAmount) as PaymentAmount 
from TableOrderPayment 
where CPaymentType='Check' and CDate<=SYSDATETIME() and CStatus='Active';

Answer 1

Select SUM(CASE When CPayment='Cash' Then CAmount Else 0 End ) as CashPaymentAmount,
       SUM(CASE When CPayment='Check' Then CAmount Else 0 End ) as CheckPaymentAmount
from TableOrderPayment
Where ( CPayment='Cash' Or CPayment='Check' ) AND CDate<=SYSDATETIME() and CStatus='Active';