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
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的,请 点击进行留言回复,万千网友为您解惑!
网友评论