如何获取学生各科最新成绩并计算平均成绩（SQL实现）

核心思路 #

你已经想到用Grades.Id判断成绩新旧（Id越大越新），核心步骤就是：先筛选出每个学生每门科目的最大Id，再通过这个Id关联回Grades表拿到对应的最新成绩，最后和Students表关联计算平均分。

具体SQL实现 #

-- 先临时存储每个学生每门科目的最新成绩ID
WITH LatestGrades AS (
    SELECT 
        Student,
        Subject,
        MAX(Id) AS LatestGradeId
    FROM Grades
    GROUP BY Student, Subject
)
-- 关联拿到最新成绩，再计算每个学生的平均分
SELECT 
    s.Id AS StudentId,
    s.Name,
    s.`Group`, -- 注意：Group是SQL关键字，MySQL用反引号包裹，SQL Server用方括号[Group]
    s.Location,
    AVG(g.Score) AS AverageScore
FROM Students s
LEFT JOIN Grades g ON s.Id = g.Student
INNER JOIN LatestGrades lg ON g.Id = lg.LatestGradeId
GROUP BY s.Id, s.Name, s.`Group`, s.Location;

代码细节说明 #

• WITH LatestGrades AS (...)：这是CTE（公共表表达式），相当于临时生成一张表，存的是每个学生每门科目里最新成绩的Id。通过GROUP BY Student, Subject把同一学生同一科目的成绩归为一组，再用MAX(Id)取每组里最大的Id（也就是最新成绩的标识）。
• LEFT JOIN Students s：保证没有成绩的学生也能出现在结果里，此时他们的平均分是NULL。
• INNER JOIN LatestGrades lg：只保留属于最新成绩的记录，自动过滤掉同一科目里旧的成绩。
• AVG(g.Score)：对筛选后的最新成绩计算平均值，GROUP BY学生的所有字段，确保每个学生只显示一行结果。

兼容旧版本数据库的写法（不用CTE） #

如果你的数据库不支持CTE（比如MySQL 5.7及以前），可以把子查询直接写在JOIN里：

SELECT 
    s.Id AS StudentId,
    s.Name,
    s.`Group`,
    s.Location,
    AVG(g.Score) AS AverageScore
FROM Students s
LEFT JOIN Grades g ON s.Id = g.Student
INNER JOIN (
    SELECT Student, Subject, MAX(Id) AS LatestGradeId
    FROM Grades
    GROUP BY Student, Subject
) lg ON g.Id = lg.LatestGradeId
GROUP BY s.Id, s.Name, s.`Group`, s.Location;

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