SQL中的case when then else end用法

case属于控制流函数，属于mysql函数的范围。类似的还有ifnull，nullif等。
使用方法：
— 简单Case函数，更简洁
CASE sex
WHEN ‘1’ THEN ‘男’
WHEN ‘2’ THEN ‘女’
ELSE ‘其他’ END

— Case搜索函数，更灵活
CASE WHEN sex = ‘1’ THEN ‘男’
WHEN sex = ‘2’ THEN ‘女’
ELSE ‘其他’ END
注意，只匹配第一个正确答案，后面的不管。

使用场景：
接select后面，修改展示内容；接group by后面，修改分组依据。
update的更新条件。
SELECT
CASE WHEN salary <= 500 THEN ‘1’
WHEN salary > 500 AND salary <= 600 THEN ‘2’
WHEN salary > 600 AND salary <= 800 THEN ‘3’
WHEN salary > 800 AND salary <= 1000 THEN ‘4’
ELSE NULL END salary_class,
COUNT(*)
FROM Table_A
GROUP BY
CASE WHEN salary <= 500 THEN ‘1’
WHEN salary > 500 AND salary <= 600 THEN ‘2’
WHEN salary > 600 AND salary <= 800 THEN ‘3’
WHEN salary > 800 AND salary <= 1000 THEN ‘4’
ELSE NULL END;

UPDATE Personnel
SET salary = CASE WHEN salary >= 5000
　 THEN salary 0.9
WHEN salary >= 2000 AND salary < 4600
THEN salary 1.15
ELSE salary END;