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

最新推荐文章于 2023-01-16 18:08:30 发布

原创最新推荐文章于 2023-01-16 18:08:30 发布 · 6.5w 阅读

19 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

29 篇文章

订阅专栏

探讨了在MySQL中使用CASE WHEN语句进行多条件判断的正确方式，尤其是在处理复杂逻辑如判断字段是否为空时的方法。文章通过实例对比了直接嵌套AND条件与通过子查询改进后的效果。

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

https://blog.youkuaiyun.com/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;