Oracle SQL查询：获取2班总平均最高且9号课程得分最高的学生

解决方案 #

方法1：基于现有代码修改 #

你可以在原有HAVING子句中新增9号课程最高分的校验条件，修改后代码如下：

SELECT s.studentname
FROM   students s
       JOIN section se
         ON s.sectionid = se.sectionid
       JOIN courses_student cs
         ON s.student_id = cs.student_id
WHERE  se.classes_id = 2
GROUP  BY s.studentname, s.student_id
HAVING 
-- 原有条件：总平均为全班最高
( Avg(cs.exam_season_one)
  + Avg(cs.exam_season_two)
  + Avg(cs.degree_season_one)
  + Avg(cs.degree_season_two) ) / 4 = (
    SELECT Max(( Avg(cs.exam_season_one)
                 + Avg(cs.exam_season_two)
                 + Avg(cs.degree_season_one)
                 + Avg(cs.degree_season_two)
               ) / 4)
    FROM   students s
           JOIN section se
             ON s.sectionid = se.sectionid
           JOIN courses_student cs
             ON s.student_id = cs.student_id
    WHERE  se.classes_id = 2
    GROUP  BY s.studentname
)
-- 新增条件：9号课程成绩为全班最高
AND (
    SELECT (exam_season_one + exam_season_two + degree_season_one + degree_season_two)/4
    FROM courses_student 
    WHERE student_id = s.student_id AND course_id =9
) = (
    SELECT MAX((exam_season_one + exam_season_two + degree_season_one + degree_season_two)/4)
    FROM students s
         JOIN section se ON s.sectionid = se.sectionid
         JOIN courses_student cs ON s.student_id = cs.student_id
    WHERE se.classes_id = 2 AND cs.course_id =9
)

这里GROUP BY新增了s.student_id，避免重名学生统计错误。

方法2：更简洁的窗口函数实现（Oracle原生支持） #

用CTE预先计算每个学生的两个核心指标，再通过排名过滤，避免重复子查询，性能和可读性都更优：

WITH student_score AS (
    SELECT 
        s.student_id,
        s.studentname,
        -- 计算全课程总平均
        (AVG(cs.exam_season_one) + AVG(cs.exam_season_two) + AVG(cs.degree_season_one) + AVG(cs.degree_season_two))/4 AS total_avg,
        -- 计算9号课程成绩，未选该课程的学生值为NULL会被后续过滤
        MAX(CASE WHEN cs.course_id =9 THEN (cs.exam_season_one + cs.exam_season_two + cs.degree_season_one + cs.degree_season_two)/4 END) AS course9_score
    FROM students s
    JOIN section se ON s.sectionid = se.sectionid
    JOIN courses_student cs ON s.student_id = cs.student_id
    WHERE se.classes_id = 2
    GROUP BY s.student_id, s.studentname
),
score_rank AS (
    SELECT 
        studentname,
        -- 总平均排名，同分并列第一
        RANK() OVER(ORDER BY total_avg DESC) AS avg_rank,
        -- 9号课程成绩排名，同分并列第一
        RANK() OVER(ORDER BY course9_score DESC) AS course9_rank
    FROM student_score
    WHERE course9_score IS NOT NULL
)
SELECT studentname
FROM score_rank
WHERE avg_rank = 1 AND course9_rank =1;

该实现会自动处理并列情况，如果有多个学生同时满足两个第一都会返回，无符合条件学生时直接返回空结果，完全匹配需求。

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