MySQL查询如何添加临时值列生成学生最终评级award

你可以通过MySQL的CASE条件表达式实现新增award评级列的需求，可根据业务规则自由调整评级的分数区间。

示例评级规则（可自行修改） #

• 得分≥85：评级为A
• 70≤得分<85：评级为B
• 60≤得分<70：评级为C
• 得分<60：评级为D
  如果需要匹配你给出的示例中54.583返回C的效果，调整对应区间的阈值即可。

修改后完整SQL语句 #

select 
  student_name,
  q4.percentage,
  case 
    when q4.percentage >= 85 then 'A'
    when q4.percentage >= 70 then 'B'
    when q4.percentage >= 60 then 'C'
    else 'D'
  end as award
  from (select q2.student_id,mark *100/total as Percentage
          from (select class_id,sum(max_mark)as total
                  from course
                 group by(class_id)
               )q1 ,
               (select sum(mark) as mark,student_id
                  from grades
                 group by(student_id)
               ) q2
         where q2.student_id in (select student_id
                                   from student
                                  where student.section_id in(select section_id 
                                                                from section 
                                                               where class_id=q1.class_id)
                                )
         order by q2.student_id
       ) q4
inner join student on q4.student_id=student.student_id;

内容的提问来源于stack exchange，提问作者mario thoumy