如何简化SQL查询中重复的平均分计算逻辑以缩短语句长度？

问题原因 #

SQL的执行顺序决定了同层级SELECT子句中定义的别名，无法在该层级的其他逻辑中直接调用，这也是你之前用AS定义别名后调用失败的核心原因。

优化方案 #

方案1：子查询实现（兼容所有关系型数据库）

先在子查询中完成平均分的计算，外层查询直接引用计算好的平均分做等级判断即可：

SELECT 
    studentname,
    CASE 
        WHEN avg_score >= 80 THEN 'EXCELLENT'
        WHEN avg_score >= 70 THEN 'VERY GOOD'
        WHEN avg_score >= 60 THEN 'GOOD'
        WHEN avg_score >= 50 THEN 'ACCEPTABLE'
        ELSE 'FAIL'
    END AS GRADE
FROM (
    SELECT 
        s.studentname,
        (AVG(cs.exam_season_one) + AVG(cs.exam_season_two) + AVG(cs.degree_season_one) + AVG(cs.degree_season_two)) / 4 AS avg_score
    FROM courses_student cs
    JOIN students s ON s.student_id = cs.student_id
    GROUP BY s.studentname
) t

方案2：CTE实现（兼容MySQL 8.0+/PostgreSQL/SQL Server等现代数据库）

可读性比子查询更好，逻辑分层更清晰：

WITH student_avg AS (
    SELECT 
        s.studentname,
        (AVG(cs.exam_season_one) + AVG(cs.exam_season_two) + AVG(cs.degree_season_one) + AVG(cs.degree_season_two)) / 4 AS avg_score
    FROM courses_student cs
    JOIN students s ON s.student_id = cs.student_id
    GROUP BY s.studentname
)
SELECT 
    studentname,
    CASE 
        WHEN avg_score >= 80 THEN 'EXCELLENT'
        WHEN avg_score >= 70 THEN 'VERY GOOD'
        WHEN avg_score >= 60 THEN 'GOOD'
        WHEN avg_score >= 50 THEN 'ACCEPTABLE'
        ELSE 'FAIL'
    END AS GRADE
FROM student_avg

额外优化建议

如果students表中student_id是唯一主键，建议将GROUP BY的字段改为s.student_id, s.studentname，避免出现重名学生成绩统计错误的问题。

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