带有 CASE 条件和 SUM() 的 SELECT 查询

2022-01-09 00:00:00 sql sum conditional-statements sql-server case

我目前正在使用这些 sql 语句.我的表有 CPaymentType 字段，其中包含现金"或支票".我可以通过执行如下所示的 2 条 SQL 语句来总结付款金额.在这种情况下，用户甚至不会注意到执行 2 条 sql 语句或仅 1 条时的速度差异，但是，我不喜欢我的方式，我只想要 1 条 sql 语句.如何使用 CASE 条件将这些重构为 1 条语句?我无法弄清楚，因为在线示例导致 1 或 0 或布尔值.我不希望包含远期支票付款.非常感谢.

I'm currently using these sql statements. My table has the field CPaymentType which contains "Cash" or "Check". I can sum up the amount of payments by executing 2 SQL statements as shown below. In this case, the user won't even notice the speed difference when executing 2 sql statements or just 1, however, I don't like my way, I just want 1 sql statement. How do I reconstruct these into 1 statement with CASE conditions? I can't figure it out since examples online result in either 1 or 0 or boolean. I don't want the postdated Check payments to be included. Thank you very much.

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';

推荐答案

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';

相关文章