case when 使用小结
1、等值转换
2、范围转换
3、行转列
1、等值转换 性别转换
select
name as '名字',
(case sex when 0 then '女' else '男' end) as '性别'
from test.student;
2、范围转换
有的时候,也会遇到这种情况,按照用户成绩显示优(90+)、良(80-90)、及格(60-80)、未及格(60-)
select
name as '姓名'
,(case score when score>=90 then '优' when score>=80 then '良' when score>=60 then '及格' else '不及格' end) as '等级'
from test.stu_score;
3、行转列
第一步 先按照科目分开, 符合条件的设置分数,不符合的给置零。
select name as '姓名'
,(case course when '语文' then score else 0 end) as '语文'
,(case course when '数学' then score else 0 end) as '数学'
,(case course when '英语' then score else 0 end) as '英语'
from test.course_score
然后再按照名字group by ,对分数求max。
select name as '姓名'
,max(case course when '语文' then score else 0 end) as '语文'
,max(case course when '数学' then score else 0 end) as '数学'
,max(case course when '英语' then score else 0 end) as '英语'
from test.course_score group by name;
本文地址:https://blog.csdn.net/m0_38017766/article/details/107639561
上一篇: 坏事了,来不及问银行存折的密码了!
下一篇: 加速触碰未来生活 人工智能将迎来黄金时代