SQL查询优化：筛选完成高分作业数量最多的学生

实现思路 #

先统计每个学生在对应课程中得分超过85分的作业数量，再筛选出其中作业数量最多的学生记录，最后按指定规则排序。

完整SQL语句 #

WITH StudentAssignmentStats AS (
    -- 统计符合条件的作业数量
    SELECT 
        s.FIRSTNAME,
        s.LASTNAME,
        c.SUBJECTCODE,
        c.COURSENUMBER,
        COUNT(a.ASSIGNMENTID) AS NUMASSIGNMENTS
    FROM 
        STUDENTS s
        JOIN ENROLLMENTS e ON s.STUDENTID = e.STUDENTID
        JOIN COURSES c ON e.COURSEID = c.COURSEID
        JOIN ASSIGNMENTS a ON e.ENROLLMENTID = a.ENROLLMENTID
    WHERE 
        a.SCORE > 85
    GROUP BY 
        s.FIRSTNAME, s.LASTNAME, c.SUBJECTCODE, c.COURSENUMBER
),
MaxAssignmentCount AS (
    -- 找出最大的符合条件作业数量
    SELECT MAX(NUMASSIGNMENTS) AS MAX_COUNT
    FROM StudentAssignmentStats
)
-- 筛选作业数量等于最大值的记录并排序
SELECT 
    sas.FIRSTNAME,
    sas.LASTNAME,
    sas.SUBJECTCODE,
    sas.COURSENUMBER,
    sas.NUMASSIGNMENTS
FROM 
    StudentAssignmentStats sas
    CROSS JOIN MaxAssignmentCount mac
WHERE 
    sas.NUMASSIGNMENTS = mac.MAX_COUNT
ORDER BY 
    sas.SUBJECTCODE,
    sas.COURSENUMBER,
    sas.LASTNAME,
    sas.FIRSTNAME;

代码说明 #

• StudentAssignmentStats：通过多表关联筛选出得分>85的作业，按学生姓名和课程分组统计作业数量。
• MaxAssignmentCount：获取统计结果中的最大作业数量，作为筛选阈值。
• 最终查询：关联两个临时结果，筛选出作业数量等于最大值的记录，并按要求排序。

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