mysql 嵌套 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

查询结果如下:

imagepng

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

阅读 6.8k
1 个回答

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

b.post_status = ''

改为

CAST(b.post_status AS CHAR) = ''

你可以再看看文档Comparison Functions and Operators

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题
宣传栏