我们在平常的开发工作中,经常会使用case when,比如我们需要将某些字段值进行转换,我们需要将数据行转列等,那么都有哪些使用场景呢?
case when 用法:如果某条数据满足了当前when,则会退出case when,不再执行后面其他case when。
1:用于字段值得转换,如:查询学生性别,表中存储的为编码:
case when 条件 then 值,when 条件 then 值 end
select name,
(case when sex = '1' then '男'
when sex = '2' then '女' end) as '性别'
from student;
case when 条件 then 值,when 条件 then 值 else 值 end
select name,
(case when sex = '1' then '男'
when sex = '2' then '女' else '' end) as '性别'
from student;
2:行转列,如查询每个学生的各科成绩,使用 case when + group by 实现:
select st.name,st.student_no,
sum(case when sc.course = '语文' then sc.score end) as '语文',
sum(case when sc.course = '数学' then sc.score end) as '数学',
sum(case when sc.course = '英语' then sc.score end) as '英语'
from score sc left join student st on sc.student_no = st.student_no GROUP BY
sc.student_no,st.name
3:使用case when统计男生和女生的数量:
select
sum(case when sex = '1' then 1 else 0 end) as '男生',
sum(case when sex = '2' then 1 else 0 end) as '女生'
from student;
4:使用case when 判断范围,比如统计各科成绩一般、良好、优秀的人数:
select course,
sum(case when score > 60 and score <=70 then 1 else 0 end) '一般',
sum(case when score > 70 and score <=80 then 1 else 0 end) '良好',
sum(case when score > 80 then 1 else 0 end) '优秀'
from score GROUP BY course;
以上为case when的基本用法,不断的学习,才会遇见更好的自己!