mysql - where 子句中的 SQL if 语句

Question

如何在我的声明中添加底线？

SELECT
AgentID,
PaymentAmount
FROM Table A

WHERE 
DATEOFPAYMENT 

GROUP BY 
CollectorNumber

ORDER BY 
CollectorNumber,
LastName,
DateofPayment,
PaymentAmount

<小时/>

if DATEOFPAYMENT = yesterday then PaymentAmount else 0 as 'previous day'

或

if DATEOFPAYMENT = current_Month then PaymentAmount else 0 as 'MTD'

或

if DATEOFPAYMENT = current_Year then PaymentAmount else 0 as 'YTD

Answer 1

我们可以研究CASE syntax from here , it would be, roughly (I am not acquainted with MySQL date formats , so please look that up )

SELECT AgentID, PaymentAmount FROM Table A

WHERE CASE DATEOFPAYMENT 
 WHEN subdate(currentDate, 1)  THEN PaymentAmount  
 WHEN month() THEN PaymentAmount 
 WHEN year()  THEN PaymentAmount 
 ELSE 0
END

GROUP BY CollectorNumber

ORDER BY CollectorNumber, LastName, DateofPayment,
PaymentAmount

这是即将完成的伪代码，因此请修复语法。但case语句是这样的。