使用MAX()结合GROUP BY的MySQL查询返回异常数据问题

当你使用GROUP BY studentCourseID时，SELECT中的studentID既不在分组字段列表里，也没有被聚合函数处理，数据库会从分组内的任意行随机取一个studentID返回——这就是日期正确但学生ID错误的核心原因：它没有把studentID和MAX(dateCreated)做对应关联，只是随便选了分组里的一个ID。

以下是几种适配不同数据库版本的可行写法：

方法1：子查询关联原表（兼容所有数据库版本） #

先找出每个课程的最新创建日期，再通过课程ID和日期关联原表，精准拿到对应最新日期的学生ID：

SELECT ca.studentID, ca.studentCourseID, ca.dateCreated
FROM courseAttempts ca
JOIN courses c ON c.courseID = ca.studentCourseID AND c.courseActive = 1
JOIN (
    SELECT studentCourseID, MAX(dateCreated) AS maxDate
    FROM courseAttempts
    WHERE studentID IN (100,101,102,103) AND studentBlocked = 0
    GROUP BY studentCourseID
) AS latest ON ca.studentCourseID = latest.studentCourseID AND ca.dateCreated = latest.maxDate
WHERE ca.studentID IN (100,101,102,103) AND ca.studentBlocked = 0

方法2：窗口函数（推荐，适用于MySQL 8.0+、PostgreSQL、SQL Server等） #

用ROW_NUMBER()给每个课程的记录按日期倒序排号，取排号为1的那条就是最新的记录：

SELECT studentID, studentCourseID, dateCreated
FROM (
    SELECT 
        ca.studentID, 
        ca.studentCourseID, 
        ca.dateCreated,
        ROW_NUMBER() OVER (PARTITION BY ca.studentCourseID ORDER BY ca.dateCreated DESC) AS rn
    FROM courseAttempts ca
    JOIN courses c ON c.courseID = ca.studentCourseID AND c.courseActive = 1
    WHERE ca.studentID IN (100,101,102,103) AND ca.studentBlocked = 0
) AS ranked
WHERE rn = 1

如果同一个课程存在多条创建日期完全相同的最新记录，想要保留所有这类记录的话，把ROW_NUMBER()换成RANK()即可。

方法3：拼接字段取最大值（仅作参考，局限性大） #

把studentID和dateCreated拼接后取最大值，再拆分回原字段，这种方法仅适用于ID和日期格式固定的场景，不推荐通用：

SELECT 
    SUBSTRING_INDEX(MAX(CONCAT(dateCreated, '|', studentID)), '|', -1) AS studentID,
    studentCourseID,
    MAX(dateCreated) AS dateCreated
FROM courseAttempts ca
JOIN courses c ON c.courseID = ca.studentCourseID AND c.courseActive = 1
WHERE ca.studentID IN (100,101,102,103) AND ca.studentBlocked = 0
GROUP BY studentCourseID

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