mysql case when多条件同时满足的多个and组合嵌套的情况,判断空is null --- 系列一

mysql case when多条件同时满足的多个and组合嵌套的情况,判断空is null — 系列

https://blog.csdn.net/qb170217/article/details/81534399

case when不支持下面2这种写法(我已经测试过了,大侠如有好办法,麻烦分享):

情况1:未加括号

select 
wf.fid id,
CASE 
WHEN wf.Fstep = 1 AND wf.Fstatus = 'Reject' and wf.Foper_reason IS NULL THEN '未填写驳回理由'
WHEN wf.Fstep = 1 AND wf.Fstatus = 'Reject' and wf.Foper_reason IS NOT NULL THEN wf.Foper_reason
END reason,
wf.Fcreate_time time
from t_work_flow wf 
order by time desc;

情况2:加括号

select 
wf.fid id,
CASE 
WHEN (wf.Fstep = 1 AND wf.Fstatus = 'Reject') and wf.Foper_reason IS NULL THEN '未填写驳回理由'
WHEN (wf.Fstep = 1 AND wf.Fstatus = 'Reject') and wf.Foper_reason IS NOT NULL THEN wf.Foper_reason
END reason,
wf.Fcreate_time time
from t_work_flow wf 
order by time desc;

更改后就可以了

SELECT id, 
CASE when reason is null THEN '未填写驳回理由' 
ELSE reason 
END reason,
time from 
(
select 
wf.fid id,
CASE 
WHEN wf.Fstep = 1 AND wf.Fstatus = 'Reject' and wf.Foper_reason IS NULL THEN '未填写驳回理由'
WHEN wf.Fstep = 1 AND wf.Fstatus = 'Reject' and wf.Foper_reason IS NOT NULL THEN wf.Foper_reason
END reason,
wf.Fcreate_time time
from t_work_flow wf
) result
order by time desc;

 

    原文作者:qb170217
    原文地址: https://blog.csdn.net/qb170217/article/details/81504578
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞