如何用SQL查询每位学生最擅长的课程（基于作业平均分）

表结构 #

• Student表：Id、Firstname、Lastname
• Home_assignments表：Student_id、Course_code、Date、Task_nr、Filename、Points、Task_id
• Course表：Code、Teacher、Course_name

任务要求 #

根据学生作业的平均分，找出每位学生最擅长的课程，输出包含Firstname、Course_name、average_best的结果。

已实现的SQL片段 #

1. 获取学生所学各课程的作业平均分：

SELECT s.lastname, s.firstname, avg(h.points) AS average_best, c.course_name
FROM course c,
     home_assignments h,
     student s
WHERE c.code = h.course_code AND h.student_id = s.id
GROUP BY c.course_name, s.lastname, s.firstname;

2. 获取学生的最高平均分，但无法关联对应课程名：

SELECT firstname, lastname, max(average_best) AS the_best 
FROM 
     (
     SELECT s.firstname, s.lastname, avg(h.points) AS average_best, c.course_name 
     FROM course c, 
          home_assignments h, 
          student s 
     WHERE c.code=h.course_code and h.student_id=s.id 
     GROUP BY c.course_name, s.lastname, s.firstname
     ) 
GROUP BY firstname, lastname;

解决方案 #

解决这类"每个分组取TopN"的问题，最简洁的方式是使用窗口函数，以下提供两种常用实现：

方式1：每个学生仅返回一门最高分课程（ROW_NUMBER()） #

如果只需要给每个学生返回一门平均分最高的课程（即使有多门课程分数相同），用ROW_NUMBER()：

WITH student_course_avg AS (
    SELECT 
        s.firstname,
        c.course_name,
        AVG(h.points) AS average_best,
        -- 按学生分组，对课程平均分降序排名
        ROW_NUMBER() OVER(PARTITION BY s.id ORDER BY AVG(h.points) DESC) AS rank_num
    FROM student s
    INNER JOIN home_assignments h ON s.id = h.student_id
    INNER JOIN course c ON h.course_code = c.code
    GROUP BY s.id, s.firstname, c.course_name
)
SELECT firstname, course_name, average_best
FROM student_course_avg
WHERE rank_num = 1;

方式2：返回所有最高分课程（RANK()） #

如果学生有多门课程平均分相同且都是最高，需要全部返回，用RANK()替代ROW_NUMBER()：

WITH student_course_avg AS (
    SELECT 
        s.firstname,
        c.course_name,
        AVG(h.points) AS average_best,
        -- 相同分数的课程会获得相同排名
        RANK() OVER(PARTITION BY s.id ORDER BY AVG(h.points) DESC) AS rank_num
    FROM student s
    INNER JOIN home_assignments h ON s.id = h.student_id
    INNER JOIN course c ON h.course_code = c.code
    GROUP BY s.id, s.firstname, c.course_name
)
SELECT firstname, course_name, average_best
FROM student_course_avg
WHERE rank_num = 1;

关键说明 #

• PARTITION BY s.id：确保按学生独立分组排序，避免跨学生排名混乱
• 使用显式INNER JOIN替代隐式连接（逗号分隔表），让SQL逻辑更清晰易读
• 窗口函数直接在分组计算平均分的同时完成排名，无需额外子查询关联，效率更高

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