mysql的多个case语句

-- 错误的示例
SELECT id, name,
       CASE status
           WHEN 'active' THEN '启用'
           WHEN 'inactive' THEN '禁用'
           ELSE '未知'
       END AS status_text
FROM users
WHERE status = 'active'; -- 这里的条件应该与CASE语句中的条件一致

-- 正确的示例
SELECT id, name,
       CASE status
           WHEN 'active' THEN '启用'
           WHEN 'inactive' THEN '禁用'
           ELSE '未知'
       END AS status_text
FROM users
WHERE status = 'active'; -- 这里的条件应该与CASE语句中的条件一致

-- 嵌套过多的示例
SELECT id, name,
       CASE
           WHEN status = 'active' THEN
               CASE
                   WHEN role = 'admin' THEN '管理员'
                   ELSE '普通用户'
               END
           ELSE '未知'
       END AS user_type
FROM users;

-- 优化后的示例
SELECT id, name,
       CASE status
           WHEN 'active' THEN
               (SELECT CASE role
                         WHEN 'admin' THEN '管理员'
                         ELSE '普通用户'
                     END
                FROM user_roles ur
                WHERE ur.user_id = u.id)
           ELSE '未知'
       END AS user_type
FROM users u;