SQL中case when then else end用法
1、认识Case
Case属于控制流函数,属于mysql函数的范围;类似的还有ifnull,nullif等
Case具有两种格式,简单Case函数和Case搜索函数
(1)简单Case函数 ,更简洁
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
(2)Case搜索函数 ,更灵活
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
注意:只匹配第一个正确答案,后面的不管
使用场景:
接select后面,修改展示内容,将group by后面,修改分组依据
2、实例
SELECT
profession ,
faculty ,
school ,
count( id ) AS 'graduate_num',
sum( CASE WHEN employment = '是' THEN 1 ELSE 0 END ) AS 'employ_num',
sum( CASE WHEN employment = '否' THEN 1 ELSE 0 END ) AS 'unemploy_num',
sum( CASE WHEN employment = '是' THEN 1 ELSE 0 END )/count( id ) AS 'rate'
FROM
student a
GROUP BY
a.school,
profession,
faculty
ORDER BY
sum( CASE WHEN employment = '是' THEN 1 ELSE 0 END )/count( id ) DESC