在JOIN的ON子句中使用SUM()函数计算学生GPA遇问题求助

我有三张表：students、student_courses和grades，需要为每位学生计算总GPA，但遇到了问题。

表结构 #

students表 #

student_courses表 #

grades表 #

报错情况 #

我的查询语句报错误码1111（无效使用分组函数），使用MySQL，也接受标准SQL解决方案：

SELECT student_id, student_name,
(select g.GPA       
 from course_student AS cs    
 inner join Grades AS g ON (sum(cs.percent_grade) / count(*)) BETWEEN g.from_percent AND g.to_percent
 where cs.student_id = students.id) As GPA 
FROM students 

预期结果 #

列出students表中所有学生及其对应的总GPA：

更新：单个学生的可行查询 #

已实现单个学生的GPA计算，但需要适配所有学生，单个学生的查询代码如下：

select g.GPA, g.from_percent, g.to_percent
 from course_student AS cs    
 inner join Grades AS g 
 where cs.student_id = 77
 group by g.GPA, g.from_percent, g.to_percent
 HAVING (sum(cs.percent_grade) / count(*)) BETWEEN g.from_percent AND g.to_percent 

错误原因 #

原查询的核心问题是：在子查询的JOIN关联条件中直接使用了sum()、count()这类分组聚合函数。聚合函数只能在GROUP BY之后的HAVING子句，或SELECT列表中使用，不能直接放在JOIN的关联条件里，这就触发了1111错误。

正确实现方式 #

要计算所有学生的总GPA，需先算出每个学生的平均百分成绩，再关联grades表匹配对应的GPA值，以下两种方式均可实现：

方法1：子查询先计算平均成绩 #

SELECT 
    s.student_id,
    s.student_name,
    g.GPA AS total_GPA
FROM 
    students s
JOIN (
    -- 先计算每个学生的平均百分成绩
    SELECT 
        student_id,
        AVG(percent_grade) AS avg_percent
    FROM 
        student_courses
    GROUP BY 
        student_id
) cs_avg ON s.student_id = cs_avg.student_id
JOIN grades g 
    ON cs_avg.avg_percent BETWEEN g.from_percent AND g.to_percent;

方法2：CTE（适用于MySQL 8.0+或支持CTE的数据库） #

WITH student_avg AS (
    SELECT 
        student_id,
        AVG(percent_grade) AS avg_percent
    FROM 
        student_courses
    GROUP BY 
        student_id
)
SELECT 
    s.student_id,
    s.student_name,
    g.GPA AS total_GPA
FROM 
    students s
JOIN student_avg sa ON s.student_id = sa.student_id
JOIN grades g ON sa.avg_percent BETWEEN g.from_percent AND g.to_percent;

结果验证 #

以学生77为例，其平均百分成绩为：
(95.7 + 85.9 + 77.1 + 66.6) / 4 = 81.325
该值落在grades表的80-82区间，对应GPA为2.67，与预期结果一致。

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