如何筛选出通过所有已报名课程的学生并计算其年度成绩？

数据表结构 #

• Students (id, name, surname, study_year, department_id)
• Courses(id, name)
• Course_Signup(id, student_id, course_id, year)
• Grades(signup_id, grade_type, mark, date)：其中grade_type取值为'e'（考试）、'l'（实验）或'p'（项目）

需求 #

展示通过所有已报名课程的学生，及其年度成绩（所有课程最终成绩的平均值）。若学生报名3门课程但仅2门最终成绩≥5，则不纳入结果。

现有SQL语句 #

SELECT
  new_table.id,
  new_table.name,
  AVG(CASE WHEN grade_type = 'e' THEN new_table.mark END) AS "Average Exam Grade",
  AVG(CASE WHEN GRADE_TYPE <> 'e' THEN new_table.mark END) AS "Average Activity Grade",
  (2*AVG(CASE WHEN grade_type = 'e' THEN new_table.mark END) + AVG(CASE WHEN GRADE_TYPE <> 'e' THEN new_table.mark END))/3 AS "Course Final Grade"
FROM
(
  SELECT s.id, c.name, g.grade_type, g.mark
    FROM Students s
    JOIN Course_Signup csn
         ON s.id = csn.student_id
    JOIN Courses c
         ON c.id = csn.course_id
    JOIN Grades g
         ON g.signup_id = csn.id
) new_table
GROUP BY new_table.id, new_table.name
HAVING ((2*AVG(CASE WHEN grade_type = 'e' THEN new_table.mark END) + AVG(CASE WHEN GRADE_TYPE <> 'e' THEN new_table.mark END))/3) >= 5.00
ORDER BY new_table.id ASC

当前语句的问题：它是将学生所有课程的考试成绩、活动成绩分别取平均后计算综合值，无法确保学生每一门已报名课程的最终成绩都≥5，只是整体综合平均达标，不符合需求。

核心思路是：先计算每门课程的最终成绩并筛选出达标的课程，再校验学生通过的课程数等于其报名的课程总数，最后计算年度成绩。

修改后的SQL如下：

SELECT
  s.id,
  s.name,
  AVG(course_final.final_grade) AS "Annual Average Grade"
FROM Students s
-- 统计每个学生报名的课程总数
JOIN (
  SELECT student_id, COUNT(*) AS total_courses
  FROM Course_Signup
  GROUP BY student_id
) signup_count ON s.id = signup_count.student_id
-- 计算每门课程的最终成绩，筛选出达标（≥5）的课程
JOIN (
  SELECT
    csn.student_id,
    csn.course_id,
    (2*AVG(CASE WHEN g.grade_type = 'e' THEN g.mark END) + AVG(CASE WHEN g.grade_type <> 'e' THEN g.mark END))/3 AS final_grade
  FROM Course_Signup csn
  JOIN Grades g ON g.signup_id = csn.id
  GROUP BY csn.student_id, csn.course_id
  HAVING (2*AVG(CASE WHEN g.grade_type = 'e' THEN g.mark END) + AVG(CASE WHEN g.grade_type <> 'e' THEN g.mark END))/3 >= 5.00
) course_final ON s.id = course_final.student_id
-- 按学生分组，校验通过课程数等于报名课程数
GROUP BY s.id, s.name, signup_count.total_courses
HAVING COUNT(course_final.course_id) = signup_count.total_courses
ORDER BY s.id ASC;

关键说明 #

1. 子查询course_final：按学生+课程维度计算每门课程的最终成绩，只保留成绩≥5的课程记录，确保单门课程达标。
2. 子查询signup_count：统计每个学生报名的课程总数，用于后续校验。
3. 最终分组校验：通过HAVING COUNT(course_final.course_id) = signup_count.total_courses确保学生通过的课程数等于报名总数，即所有报名课程都达标。

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