如何按学校分组批量划分学生：教师学生不拆分且批次不超100人

核心规则回顾 #

• 按School分组划分批次
• 同一教师的12名学生必须归属同一批次，不可拆分
• 单批次最大容量100人，允许人数不足100，但绝对不能超过

问题根源 #

原查询直接按学生行累加计数，未考虑教师组的整体性——当累计人数接近100时，加入整个教师组（12人）可能会超过100，但原逻辑仍将其归入当前批次，导致批次人数超标。

修改后的SQL实现 #

WITH TeacherGroups AS (
    -- 按学校+教师分组，获取每个教师组的学生数，在学校内给教师组排序
    SELECT 
        School,
        Teacher,
        COUNT(*) AS StudentCount,
        ROW_NUMBER() OVER (PARTITION BY School ORDER BY Teacher) AS TeacherRowNum
    FROM Students
    GROUP BY School, Teacher
),
RecursiveBatch AS (
    -- 初始化：每个学校的第一个教师组归为第1批次
    SELECT 
        School,
        Teacher,
        StudentCount,
        TeacherRowNum,
        StudentCount AS BatchTotal,
        1 AS BatchNumber
    FROM TeacherGroups
    WHERE TeacherRowNum = 1

    UNION ALL

    -- 递归分配批次：判断加入当前教师组后是否超100，决定是否开新批次
    SELECT 
        t.School,
        t.Teacher,
        t.StudentCount,
        t.TeacherRowNum,
        CASE WHEN r.BatchTotal + t.StudentCount <= 100 
             THEN r.BatchTotal + t.StudentCount
             ELSE t.StudentCount
        END AS BatchTotal,
        CASE WHEN r.BatchTotal + t.StudentCount <= 100 
             THEN r.BatchNumber
             ELSE r.BatchNumber + 1
        END AS BatchNumber
    FROM TeacherGroups t
    JOIN RecursiveBatch r ON t.School = r.School AND t.TeacherRowNum = r.TeacherRowNum + 1
)
-- 关联学生表，输出每个学生的批次号
SELECT 
    s.*,
    rb.BatchNumber
FROM Students s
JOIN RecursiveBatch rb ON s.School = rb.School AND s.Teacher = rb.Teacher
ORDER BY s.School, rb.BatchNumber, s.Teacher;

代码说明 #

1. TeacherGroups CTE：先聚合每个学校下的教师组，统计每组学生数（需求中固定为12），并给教师组排序，保证批次分配顺序一致。
2. RecursiveBatch 递归CTE：核心逻辑，逐组分配批次：
   • 初始行：每个学校的第一个教师组归入第1批次，批次总人数为该组学生数。
   • 递归步骤：判断当前教师组加入上一批次后是否超100人。不超则归入当前批次并累加人数；超则开启新批次，批次总人数重置为当前组学生数。
3. 最终关联：将学生表与递归生成的教师组批次信息关联，得到每个学生的批次号。

关键改进 #

• 以教师组为单位分配批次，从根源避免同一教师的学生被拆分。
• 递归逻辑严格控制批次总人数≤100，彻底解决原查询中批次超容的问题。

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