当前位置:   article > 正文

mysql case when in_【mysql】mysql 嵌套 case when 的问题?

mysql case when里嵌套case when为什么不执行

sql语句如下:

SELECT

a.uc_id id,

(

CASE WHEN a.uc_realname IS NULL OR a.uc_realname =''

THEN a.uc_sys_name ELSE a.uc_realname END

) AS realName,

a.uc_register_time registerTime,

a.uc_phone phone,

a.uc_last_login_time lastLoginTime,

(

CASE

WHEN LEFT (a.uc_code, 2) = '00' THEN

'地推会员'

ELSE

'普通会员'

END

) type,

(

CASE

WHEN a.uc_flag = 0 THEN

'未确认'

WHEN a.uc_flag = 1 THEN

'有效'

WHEN a.uc_flag = 2 THEN

'无效'

END

) flagName,

b.post_status,

(

CASE

WHEN b.post_status IS NULL OR b.post_status = ''

THEN

(

CASE

WHEN tmp.ctime IS NOT NULL AND tmp.ctime <> ''

THEN '新投递'

ELSE '暂无' END

)

ELSE

(

CASE

WHEN b.post_status = 0

THEN '已预约面试'

WHEN b.post_status = 1

THEN '面试成功'

WHEN b.post_status = 2

THEN '已拒绝'

WHEN b.post_status = 3

THEN '已失效'

ELSE '未知' END

)

END

) `status`,

tmp.ctime,

b.memo,

c.realname manageRealName,

c.id manageUseId

FROM

bco_uc.uc_app_user a

LEFT JOIN bco_web.web_crm_dz b ON a.uc_id = b.app_user_id

LEFT JOIN bco_web.web_manage_user c ON b.manage_user_id=c.id

LEFT JOIN (

SELECT

t.uc_id,

MAX(t1.create_time) ctime

FROM

bco_uc.uc_app_user t

LEFT JOIN bco_jobpost.job_position_apply t1 ON t.uc_id = t1.user_id COLLATE utf8mb4_unicode_ci

WHERE t1.status='draft' GROUP BY t.uc_id

) tmp ON a.uc_id = tmp.uc_id

查询结果如下:

253c2a807e3c990f18ae1d66f88cefa5.png

有没有发现case when的最外层的判断没有生效,这是为什么?

回答

因为在Mysql里,整数0与空串”做等于比较的时候,结果为真,你需要把

b.post_status = ''

改为

CAST(b.post_status AS CHAR) = ''

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/Monodyee/article/detail/485195
推荐阅读
相关标签
  

闽ICP备14008679号