如何获取拥有最大Year和Sem的所有ID？SQL技术问询

嗨，我来帮你梳理下这个问题，你的原SQL语句存在几个小问题，导致没法得到你想要的结果：

首先看你的原代码：

SELECT m.ID, MAX(m.Year) as MaxYearLevel, m.Sem 
FROM (SELECT t1.ID AS StudentID, t1.Year, t1.Sem FROM table1 t1) AS m 
GROUP BY m.ID;

这里的问题是：

• 当你按m.ID分组后，m.Sem既不是分组列也没有用聚合函数包裹，在大多数严格的SQL模式下（比如MySQL开启ONLY_FULL_GROUP_BY）会直接报错，就算不报错，返回的Sem值也是随机的，不是对应最大Year的那个Sem。
• 这个语句只能拿到每个ID的最大Year，但没法关联到该Year下的最大Sem，不符合你“具备最大Year和Sem”的需求。

接下来分两种常见场景给你解决方案：

场景1：获取每个ID对应的最新学期（最大Year + 该Year下的最大Sem） #

如果你的需求是每个学生ID的最新学期记录，推荐用窗口函数ROW_NUMBER()，这是最直观且高效的方式：

WITH ranked_students AS (
    SELECT 
        ID,
        Year,
        Sem,
        -- 按ID分组，给每个ID的记录按Year降序、Sem降序排名
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Year DESC, Sem DESC) AS rn
    FROM table1
)
-- 取每个ID排名第1的记录（也就是最新的学期）
SELECT ID, Year, Sem
FROM ranked_students
WHERE rn = 1;

如果你的数据库不支持CTE（比如老版本MySQL），可以用子查询关联的方式：

SELECT t1.ID, t1.Year, t1.Sem
FROM table1 t1
INNER JOIN (
    -- 先找出每个ID的最大Year，以及该Year下的最大Sem
    SELECT 
        ID,
        MAX(Year) AS max_year,
        MAX(Sem) AS max_sem
    FROM table1
    GROUP BY ID
) t2 
ON t1.ID = t2.ID 
AND t1.Year = t2.max_year 
AND t1.Sem = t2.max_sem;

场景2：获取整个表中最新学期（全局最大Year + 该Year下的最大Sem）的所有ID #

如果你的需求是找出所有处于整个表最新学期的学生ID，可以这样写：

SELECT ID, Year, Sem
FROM table1
WHERE (Year, Sem) = (
    -- 先找到全局最大的Year，再在该Year下找最大的Sem
    SELECT Year, Sem
    FROM table1
    ORDER BY Year DESC, Sem DESC
    LIMIT 1
);

或者用子查询嵌套的方式，兼容性更好：

SELECT ID, Year, Sem
FROM table1
WHERE Year = (SELECT MAX(Year) FROM table1)
  AND Sem = (SELECT MAX(Sem) FROM table1 WHERE Year = (SELECT MAX(Year) FROM table1));

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