多表连接查询显示课程名用RANK获取最高平均分单条记录

AI 小北

2026-8-28

SQL问题解法

改动说明

两个需求对应调整逻辑如下：

展示课程名称：原查询仅关联了学生表和成绩表，未关联存储课程信息的courses表，新增关联条件JOIN courses c ON g.course_id = c.course_id后，直接在查询列中取c.course_name即可，分组时需将课程名同步加入GROUP BY子句。如果后续需要限定查询Geometry单门课程，可增加过滤条件WHERE c.course_name = 'Geometry'，本次测试数据所有成绩均属于Geometry课程，加不加该过滤条件结果一致。
筛选平均分最高的记录：先计算每个学生的课程平均分，再使用DENSE_RANK()窗口函数（取第一名的场景下RANK()效果完全一致）按平均分倒序生成排名，最后在外层筛选排名值为1的记录即可。该写法会自动返回所有并列第一的结果，如果要求忽略并列、强制仅返回1条记录，可将窗口函数替换为ROW_NUMBER()。

完整可运行代码

WITH student_course_avg AS (
    SELECT 
        s.student_id,
        s.first_name,
        s.last_name,
        c.course_name,
        ROUND(AVG(g.grade), 1) AS student_avg
    FROM students s
    INNER JOIN grades g
        ON s.student_id = g.student_id
    INNER JOIN courses c
        ON g.course_id = c.course_id
    -- 如需指定仅查询Geometry课程，放开下方注释即可
    -- WHERE c.course_name = 'Geometry'
    GROUP BY s.student_id, s.first_name, s.last_name, c.course_name
)
SELECT 
    student_id,
    first_name,
    last_name,
    course_name,
    student_avg
FROM (
    SELECT 
        *,
        DENSE_RANK() OVER(ORDER BY student_avg DESC) AS score_rank
    FROM student_course_avg
) t
WHERE score_rank = 1;