mysql中case的用法

CASE column_name
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE result
END

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE result
END

SELECT id, name, department,
    CASE department
        WHEN 'HR' THEN 'Human Resources'
        WHEN 'IT' THEN 'Information Technology'
        ELSE 'Other'
    END AS department_name
FROM employees;

SELECT id, name, salary,
    CASE
        WHEN salary > 5000 THEN salary * 0.1
        WHEN salary > 3000 THEN salary * 0.05
        ELSE 0
    END AS bonus
FROM employees;

-- 错误示例
SELECT * FROM employees
WHERE CASE WHEN salary > 5000 THEN 'High' ELSE 'Low' END = 'High';

-- 正确示例
SELECT * FROM employees
WHERE salary > 5000;

-- 错误示例
SELECT department, AVG(CASE WHEN salary > 5000 THEN NULL ELSE salary END) AS avg_salary
FROM employees
GROUP BY department;

-- 正确示例
SELECT department, AVG(CASE WHEN salary > 5000 THEN salary ELSE NULL END) AS avg_salary
FROM employees
GROUP BY department;