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
查詢結果如下：
有沒有發現case when的最外層的判斷沒有生效，這是為什麼？
回答
因為在Mysql裡，整數0與空串”做等于比較的時候，結果為真，你需要把
b.post_status = ''
改為
CAST(b.post_status AS CHAR) = ''