题解 | #学生成绩分析#

比较经典的题目，在实际工作中运用挺多的。先要考虑符合每个人符合哪类条件的，在通过条件筛选人数。
方法一,if做法
select
t.等级,
count(学生编号) as "学生人数"
from(
select 
学生编号,
if(分数 between 86 and 100,"优秀",if(分数 between 71 and 85,"良好",if(分数 between 60 and 70 ,"及格","不及格"))) as 等级
from 学生分数表
) as t 
group by t.等级

方法二 case when then end做法
select 
t.等级,
count(学生编号) as "学生人数"
from(
select 
学生编号,
case when 分数 between 86 and 100 then "优秀"
     when 分数 between 71 and 85 then "良好"
     when 分数 between 60 and 70 then "及格"
     when 分数<60 then "不及格" end  as 等级
from 学生分数表
) as t 
group by t.等级