MySQL基于计算列最大值实现LEFT JOIN查询学生最新班级

原查询存在的问题 #

1. 字段名错误：students和history表的学生ID字段为id_number，但原查询中误用了idno，会导致表关联失败。
2. 逻辑偏差：子查询未关联具体学生，无法针对每个用户筛选出其最新的学期记录，临时表ext的使用方式也不符合SQL语法规范。

正确实现方案 #

方案1：使用窗口函数（推荐，适配多数现代数据库）

窗口函数可以高效地按学生分组，筛选出每组内最新的学期记录：

SELECT id_number, section
FROM (
    SELECT 
        s.id_number,
        h.section,
        -- 按学生分组，以学期排序值降序分配序号，最新记录序号为1
        ROW_NUMBER() OVER (
            PARTITION BY s.id_number 
            ORDER BY (t.year * 3 + t.term) DESC
        ) AS rn
    FROM students s
    JOIN history h ON s.id_number = h.id_number
    JOIN terms t ON h.terms_id = t.id
) AS ranked_records
WHERE rn = 1;

方案2：关联子查询（适配不支持窗口函数的旧版数据库）

通过子查询获取每个学生的最大学期排序值，再匹配对应班级：

SELECT s.id_number, h.section
FROM students s
JOIN history h ON s.id_number = h.id_number
JOIN terms t ON h.terms_id = t.id
WHERE (t.year * 3 + t.term) = (
    -- 子查询：获取当前学生的最大学期排序值
    SELECT MAX(t2.year * 3 + t2.term)
    FROM history h2
    JOIN terms t2 ON h2.terms_id = t2.id
    WHERE h2.id_number = s.id_number
);

方案说明 #

两种方案都基于year*3+term的排序规则，确保学期顺序（1st Sem > 2nd Sem > Summer）的正确性，且能自动适配未来新增的学生、学期及班级数据。

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