SQL 带有 CASE 条件和 SUM() 的 SELECT 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20935265/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT query with CASE condition and SUM()
提问by chris_techno25
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.
我目前正在使用这些 sql 语句。我的表有字段 CPaymentType，其中包含“现金”或“支票”。我可以通过执行 2 个 SQL 语句来总结支付金额，如下所示。在这种情况下，用户甚至不会注意到执行 2 条 sql 语句或仅执行 1 条时的速度差异，但是，我不喜欢我的方式，我只想要 1 条 sql 语句。如何使用 CASE 条件将这些重构为 1 个语句？我无法弄清楚，因为在线示例导致 1 或 0 或布尔值。我不希望包含过期的支票付款。非常感谢。
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';
回答by Mudassir Hasan
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';
回答by simon at rcl
select CPaymentType, sum(CAmount)
from TableOrderPayment
where (CPaymentType = 'Cash' and CStatus = 'Active')
or (CPaymentType = 'Check' and CDate <= bsysdatetime() abd CStatus = 'Active')
group by CPaymentType
Cheers -
干杯——
回答by Galz
To get each sum in a separate column:
要在单独的列中获取每个总和：
Select SUM(IF(CPaymentType='Check', CAmount, 0)) as PaymentAmountCheck,
SUM(IF(CPaymentType='Cash', CAmount, 0)) as PaymentAmountCash
from TableOrderPayment
where CPaymentType IN ('Check','Cash')
and CDate<=SYSDATETIME()
and CStatus='Active';
回答by mmarie
I don't think you need a case statement. You just need to update your where clause and make sure you have correct parentheses to group the clauses.
我认为您不需要案例陈述。您只需要更新您的 where 子句并确保您有正确的括号来对子句进行分组。
SELECT Sum(CAMount) as PaymentAmount
from TableOrderPayment
where (CStatus = 'Active' AND CPaymentType = 'Cash')
OR (CStatus = 'Active' and CPaymentType = 'Check' and CDate<=SYSDATETIME())
The answers posted before mine assume that CDate<=SYSDATETIME() is also appropriate for Cash payment type as well. I think I split mine out so it only looks for that clause for check payments.
我之前发布的答案假设 CDate<=SYSDATETIME() 也适用于现金支付类型。我想我把我的分开了，所以它只寻找支票付款的条款。
回答by Charles Bretana
Use an "Or"
使用“或”
Select SUM(CAmount) as PaymentAmount
from TableOrderPayment
where (CPaymentType='Check' Or CPaymentType='Cash')
and CDate <= case CPaymentType When 'Check' Then SYSDATETIME() else CDate End
and CStatus='" & "Active" & "'"
or an "IN"
或“IN”
Select SUM(CAmount) as PaymentAmount
from TableOrderPayment
where CPaymentType IN ('Check', 'Cash')
and CDate <= case CPaymentType When 'Check' Then SYSDATETIME() else CDate End
and CStatus='" & "Active" & "'"
