SQL按成绩等级统计并排序学生选课数 兼容无选课学生

原代码问题溯源 #

• point_score=2、3的统计用了无关联的子查询，统计的是全校该等级课程的总数量，不是单个学生的选课数
• 全部使用INNER JOIN，未选课的学生因为没有Registration关联记录会被直接过滤
• 按student_name分组，遇到重名学生时会被错误合并，用MAX(student_id)的处理也无法避免统计失真

修正后可运行代码 #

SELECT 
    s.student_id AS ID,
    s.student_name AS NAME,
    SUM(CASE WHEN sub.point_score = 1 THEN 1 ELSE 0 END) AS Point1,
    SUM(CASE WHEN sub.point_score = 2 THEN 1 ELSE 0 END) AS Point2,
    SUM(CASE WHEN sub.point_score = 3 THEN 1 ELSE 0 END) AS Point3
FROM Student s
LEFT JOIN Registration r 
    ON s.student_id = r.student_id
LEFT JOIN Subject sub 
    ON r.subject_id = sub.subject_id
GROUP BY s.student_id, s.student_name
ORDER BY s.student_id;

修正逻辑说明 #

• 采用左连接（LEFT JOIN）关联选课表和课程表，没有选课的学生也会保留在结果中，关联字段为NULL
• 用条件聚合替代原有的子查询，逐行判断每个选课记录对应的成绩等级，求和后就是单个学生对应等级的选课数，未选课的学生所有统计值自动为0
• 以Student表的主键student_id作为分组依据，配合student_name，避免重名学生被错误合并

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