mysql的casewhen语句转oraclewhere子句条件如下：wherea.xxx='asdf'and(casewhen(b.ilike'%0%')thentruewhen(b.ilike'%1%')thenc.creatorCode=#code#when(b.ilike'%2%')then(d.createDept=#dept#and#flag#=1)elsefalseend)

Question

mysql的case when语句转oracle
where子句条件如下：
where a.xxx = 'asdf' and (
case
when (b.i like '%0%') then true
when (b.i like '%1%') then c.creatorCode = #code#
when (b.i like '%2%') then (d.createDept = #dept# and #flag# = 1)
else false
end
)
这语句是改过,以免泄漏公司业务,只表示逻辑上是这个意思.
like让人有些无奈,b.i可以012都有,显然逻辑是如果有0,不管是否有1,2,用条件1判断,没有0,但有1,则用条件2,0,1都没有但有2,则用条件3.

Answer 1

oracle也有case when的语法,照办就行,不过用在where条件中,需要有返回值的比较.
这是根据你提供的语句修改,给你提供一个参考吧：
where a.xxx = 'asdf' and (
case
when (b.i like '%0%') then true
when (b.i like '%1%') and c.creatorCode = #code# then true
when (b.i like '%2%') and (d.createDept = #dept# and #flag# = 1) then true
else false
end) = true
这是按照我的业务理解写的
where a.xxx = 'asdf' and (
case
when (b.i like '%0%') then 1
when (b.i like '%1%') and c.creatorCode = #code# then 2
when (b.i like '%2%') and (d.createDept = #dept# and #flag# = 1) then 3
else 4
end) = 1 -- (根据反回结果来过滤数据）