SQL多表查询优化：筛选学生唯一报名记录并按总分排序

针对你的三个需求，我们可以通过窗口函数分步实现，完全覆盖你提到的所有要求，包括按班级录取人数限制的功能。

需求拆解与实现思路 #

1. 每个学生仅显示最早的报名记录 #

首先，我们需要给每个学生的所有报名记录按id_register（插入时间）排序，取第一条（最早的）。这里用ROW_NUMBER()窗口函数，按学生ID分组，按id_register升序排列，标记每条记录的序号，取序号为1的记录即可。

2. 计算学生总成绩total #

原公式(student.score_a*50/100)+(student.score_b*50/100)可以简化为(score_a + score_b)/2，计算结果完全一致，写法更简洁。

3. 按班级录取人数限制 #

在获取每个学生最早报名记录的基础上，我们再按班级分组，给每个班级的学生按total降序排序，然后根据class表的limit_people字段，保留每个班级排名前limit_people的学生。这一步同样用ROW_NUMBER()窗口函数实现。

完整SQL代码 #

WITH student_first_register AS (
    -- 第一步：获取每个学生最早的报名记录
    SELECT 
        r.id_register,
        r.id_students,
        r.id_class,
        s.score_a,
        s.score_b,
        -- 按学生分组，按报名记录ID升序排序，标记序号
        ROW_NUMBER() OVER (PARTITION BY r.id_students ORDER BY r.id_register ASC) AS rn
    FROM register r
    LEFT JOIN student s ON r.id_students = s.id_student
),
ranked_by_class AS (
    -- 第二步：给每个班级的学生按总成绩降序排名，关联班级人数限制
    SELECT 
        fr.id_register,
        fr.id_students,
        (fr.score_a + fr.score_b)/2 AS total,
        fr.id_class,
        c.limit_people,
        -- 按班级分组，按总成绩降序排序，标记班级内排名
        ROW_NUMBER() OVER (PARTITION BY fr.id_class ORDER BY (fr.score_a + fr.score_b)/2 DESC) AS class_rank
    FROM student_first_register fr
    LEFT JOIN class c ON fr.id_class = c.id_class
    WHERE fr.rn = 1 -- 仅保留每个学生最早的报名记录
)
-- 第三步：筛选每个班级排名不超过人数限制的学生，按总成绩降序输出
SELECT 
    id_register,
    id_students,
    total,
    id_class
FROM ranked_by_class
WHERE class_rank <= limit_people
ORDER BY total DESC;

查询结果验证 #

执行上述SQL后，得到的结果完全匹配你期望的目标表：

说明：

• 班级1的limit_people为2，总成绩75的学生1和3都被录取；
• 班级2的limit_people为2，目前只有学生2（总成绩80）符合，所以保留；
• 班级3的limit_people为1，学生4（总成绩75）是该班级唯一符合条件的学生，被录取。

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