本文介绍了一种在SQL查询中根据特定字段值动态调整WHERE子句条件的方法，通过使用CASE语句来实现对不同状态下的贷款记录进行筛选，特别是当状态为520时忽略phaseNumber与repayedTermCount之间的比较。
原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
扫一扫
1826
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
