mysql - SQl if语句在where子句中

如何在报表中增加底线？

SELECT
AgentID,
PaymentAmount
FROM Table A

WHERE
DATEOFPAYMENT

GROUP BY
CollectorNumber

ORDER BY
CollectorNumber,
LastName,
DateofPayment,
PaymentAmount

  如果DATEOFPAYMENT =昨天，则PaymentAmount否则为0“前一天”


要么


  如果DATEOFPAYMENT = current_Month，则PaymentAmount否则为0，表示“ MTD”


要么


  如果DATEOFPAYMENT = current_Year，则PaymentAmount否则为0，表示“ YTD”

最佳答案

我们可以研究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

这是几乎完成的伪代码，因此请修复语法。但是案情陈述是这样的。