如何按班级人数限制筛选选课记录并优先显示未饱和班级

这个需求的核心是在班级名额限制下，为每个学生分配一条选课记录，优先保留高分学生的名额，同时确保班级不超员。这不是简单的静态排名能解决的——因为一个学生只能占用一个班级的名额，当高分学生选定班级后，剩下的名额需要重新分配给其他学生。

解决方案：递归CTE模拟名额分配过程 #

我们可以用递归CTE来模拟逐步分配名额的过程，优先处理总分高的学生，让他们先选择有剩余名额的班级，再依次处理后续学生：

WITH RECURSIVE
-- 第一步：整理所有学生的选课记录，计算总分，并给每个学生的选课班级排序（确定优先选择顺序）
student_classes AS (
    SELECT 
        r.id_register,
        r.id_students,
        (s.score_a + s.score_b) * 50 / 100 AS total,
        r.id_class,
        c.limit_people,
        -- 给每个学生的选课班级分配优先级（这里按班级ID升序，你也可以根据需求调整）
        ROW_NUMBER() OVER (PARTITION BY r.id_students ORDER BY r.id_class) AS class_order
    FROM register r
    LEFT JOIN student s ON r.id_students = s.id_student
    LEFT JOIN class c ON r.id_class = c.id_class
),
-- 第二步：按学生总分降序排序，确定分配优先级（总分高的先选班级）
sorted_students AS (
    SELECT DISTINCT id_students, total
    FROM student_classes
    ORDER BY total DESC, id_students ASC
),
-- 第三步：初始化班级的剩余名额
class_quota AS (
    SELECT id_class, limit_people AS remaining_quota
    FROM class
),
-- 第四步：递归分配名额，每次处理一个学生，分配其第一个有剩余名额的班级
allocation AS (
    -- 初始状态：处理第一个（总分最高的）学生
    SELECT 
        sc.id_register,
        sc.id_students,
        sc.total,
        sc.id_class,
        cq.remaining_quota - 1 AS new_quota,
        1 AS step
    FROM student_classes sc
    JOIN sorted_students ss ON sc.id_students = ss.id_students
    JOIN class_quota cq ON sc.id_class = cq.id_class
    WHERE ss.id_students = (SELECT MIN(id_students) FROM sorted_students)
        AND sc.class_order = 1
        AND cq.remaining_quota > 0
    
    UNION ALL
    
    -- 递归处理后续学生
    SELECT 
        sc.id_register,
        sc.id_students,
        sc.total,
        sc.id_class,
        cq.remaining_quota - 1 AS new_quota,
        a.step + 1 AS step
    FROM allocation a
    -- 获取下一个待处理的学生（按总分降序）
    JOIN sorted_students ss ON ss.id_students = (
        SELECT MIN(id_students) 
        FROM sorted_students 
        WHERE id_students > a.id_students
    )
    JOIN student_classes sc ON sc.id_students = ss.id_students
    -- 获取当前所有班级的剩余名额（更新之前分配后的状态）
    JOIN (
        SELECT 
            cq.id_class,
            COALESCE(MAX(a2.new_quota), cq.limit_people) AS remaining_quota
        FROM class_quota cq
        LEFT JOIN allocation a2 ON cq.id_class = a2.id_class
        GROUP BY cq.id_class, cq.limit_people
    ) cq ON sc.id_class = cq.id_class
    -- 找到该学生第一个有剩余名额的班级
    WHERE sc.class_order = (
        SELECT MIN(sc2.class_order)
        FROM student_classes sc2
        JOIN (
            SELECT 
                cq2.id_class,
                COALESCE(MAX(a2.new_quota), cq2.limit_people) AS remaining_quota
            FROM class_quota cq2
            LEFT JOIN allocation a2 ON cq2.id_class = a2.id_class
            GROUP BY cq2.id_class, cq2.limit_people
        ) cq2 ON sc2.id_class = cq2.id_class
        WHERE sc2.id_students = ss.id_students
            AND cq2.remaining_quota > 0
    )
)
-- 输出最终分配结果，按总分降序排序
SELECT id_register, id_students, total, id_class
FROM allocation
ORDER BY total DESC, id_class ASC;

逻辑解释 #

1. student_classes：整理所有选课记录，计算学生总分，并给每个学生的选课班级标记选择顺序（这里按班级ID排序，你可以根据需求调整，比如优先选择学生最早选课的班级）。
2. sorted_students：按总分从高到低排序学生，确保高分学生先获得名额分配权。
3. class_quota：初始化每个班级的剩余名额。
4. allocation递归CTE：
   • 初始步骤：处理总分最高的学生，分配其第一个有剩余名额的班级，并更新该班级的剩余名额。
   • 递归步骤：依次处理下一个学生，每次查询当前班级的剩余名额，给学生分配第一个还有名额的班级，更新班级剩余名额。
5. 最后输出分配结果，按总分降序排序，和你期望的结果完全一致。

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