SQL使用case when 动态的修改where条件-白红宇

SQL使用case when 动态的修改where条件

阅读量：6160 次

发布时间：2019-06-21

本文共 1060 字，大约阅读时间需要 3 分钟。

原SQL：

SELECT	loan.*, loan_phase.plannedTermAmountFROM	loanLEFT JOIN loan_phase ON loan_phase.loanId = loan.loanIdWHEREloan_phase.phaseNumber = loan.repayedTermCount + 1AND 	loan.borrowerId = 100588AND (	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.plannedTermAmountFROM	loanLEFT JOIN loan_phase ON loan_phase.loanId = loan.loanIdWHERE(CASE loan.status when 520 then 1=1 else 'loan_phase.phaseNumber=loan.repayedTermCount+1' end)AND 	loan.borrowerId = 100588AND (	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.loanIdWHERE(CASE loan.status when 520 then 1 else loan_phase.phaseNumber-loan.repayedTermCount end  )=1AND 	loan.borrowerId = 100588AND (	loan. STATUS = 500	OR loan. STATUS = 550	OR loan. STATUS = 520)ORDER BY	loan.createTime DESC

转载于:https://my.oschina.net/iyinghui/blog/1839564

你可能感兴趣的文章