如何获取每个studid的最新专业记录？SQL查询需求

原始表数据 #

原始semester_student表数据如下（'S'代表夏季学期）：

结果一：每个studid仅保留最新学年学期的一条记录 #

实现SQL #

SELECT s.sy, s.sem, s.studid, s.studmajor
FROM semester_student s
INNER JOIN (
    SELECT studid, 
           MAX(CONCAT(sy, CASE sem WHEN 'S' THEN '3' ELSE sem END)) AS latest_term
    FROM semester_student
    GROUP BY studid
) t ON s.studid = t.studid 
   AND CONCAT(s.sy, CASE s.sem WHEN 'S' THEN '3' ELSE s.sem END) = t.latest_term
ORDER BY s.studid;

逻辑说明 #

• 子查询将夏季学期S转换为3（匹配学期先后顺序：1→2→S），拼接学年与转换后的学期字符串，通过MAX()获取每个学生的最新学期标识。
• 主查询通过学生ID和最新学期标识关联，精准筛选出对应记录。

结果二：保留每个studid当前专业的所有学期记录 #

实现SQL #

SELECT s.sy, s.sem, s.studid, s.studmajor
FROM semester_student s
INNER JOIN (
    SELECT studid, studmajor
    FROM semester_student
    WHERE CONCAT(sy, CASE sem WHEN 'S' THEN '3' ELSE sem END) = (
        SELECT MAX(CONCAT(sy, CASE sem WHEN 'S' THEN '3' ELSE sem END))
        FROM semester_student t
        WHERE t.studid = semester_student.studid
    )
) t ON s.studid = t.studid AND s.studmajor = t.studmajor
ORDER BY s.studid, s.sy DESC, 
         CASE s.sem WHEN 'S' THEN 3 ELSE CAST(s.sem AS UNSIGNED) END DESC;

逻辑说明 #

• 子查询先获取每个学生最新学期对应的专业（即当前专业）。
• 主查询通过学生ID和专业关联，筛选出该学生所有属于当前专业的记录，最后按学生ID、学年倒序、学期倒序排序（转换S为3保证排序逻辑正确）。

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