如何基于AVG平均成绩使用CASE函数计算对应字母等级

实现逻辑 #

你需要先完成学生平均成绩的聚合计算，再基于聚合后的平均成绩做等级匹配，不能将等级判断逻辑和聚合计算放在同一查询层级。

代码实现 #

方法1：子查询实现 #

SELECT 
  student_name,
  avg_grade,
  CASE 
    WHEN avg_grade >= 90 THEN 'A'
    WHEN avg_grade >= 80 THEN 'B'
    WHEN avg_grade >= 70 THEN 'C'
    WHEN avg_grade >= 60 THEN 'D'
    ELSE 'F' 
  END AS letter_grade
FROM (
  -- 内层先聚合计算每个学生的平均成绩
  SELECT 
    student_name,
    AVG(score) AS avg_grade
  FROM grades
  GROUP BY student_name
) AS student_avg_scores;

方法2：CTE实现（适配可汗学院SQLite编译环境） #

WITH student_avg_scores AS (
  SELECT 
    student_name,
    AVG(score) AS avg_grade
  FROM grades
  GROUP BY student_name
)
SELECT 
  student_name,
  avg_grade,
  CASE 
    WHEN avg_grade >= 90 THEN 'A'
    WHEN avg_grade >= 80 THEN 'B'
    WHEN avg_grade >= 70 THEN 'C'
    WHEN avg_grade >= 60 THEN 'D'
    ELSE 'F' 
  END AS letter_grade
FROM student_avg_scores;

错误原因说明 #

你之前遇到的问题是因为将CASE判断写在了聚合查询的同一层级，此时CASE是对每行单科成绩做判断，分组后仅会返回随机一行单科成绩对应的等级，和聚合后的平均成绩无关联，因此结果不符合预期。

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