java中case when用法,SQL使用case when 动态的修改where条件

原SQL：

SELECT

loan.*, loan_phase.plannedTermAmount

FROM

loan

LEFT JOIN loan_phase ON loan_phase.loanId = loan.loanId

WHERE

loan_phase.phaseNumber = loan.repayedTermCount + 1

AND

loan.borrowerId = 100588

AND (

loan. STATUS = 500

OR loan. STATUS = 550

OR loan. STATUS = 520

)

ORDER BY

loan.createTime DESC

上面sql不满足条件，要求如果status=520，那么就要忽略where条件：loan_phase.phaseNumber = loan.repayedTermCount + 1

解决办法1：

SELECT

loan.*, loan_phase.plannedTermAmount

FROM

loan

LEFT JOIN loan_phase ON loan_phase.loanId = loan.loanId

WHERE

(CASE loan.status when 520 then 1=1 else 'loan_phase.phaseNumber=loan.repayedTermCount+1' end)

AND

loan.borrowerId = 100588

AND (

loan. STATUS = 500

OR loan. STATUS = 550

OR loan. STATUS = 520

)

ORDER BY

loan.createTime DESC

或者：

LEFT JOIN loan_phase ON loan_phase.loanId = loan.loanId

WHERE

(CASE loan.status when 520 then 1 else loan_phase.phaseNumber-loan.repayedTermCount end )=1

AND

loan.borrowerId = 100588

AND (

loan. STATUS = 500

OR loan. STATUS = 550

OR loan. STATUS = 520

)

ORDER BY

loan.createTime DESC