MySQL左表每行仅关联右表最新一行的查询正确性验证

我需要实现左表每一行仅关联右表中按日期时间字段取最新的一行（比如右表同关联项有多条记录时，只取最新的一条和左表行关联）。目前我写了示例查询语句，能得到预期结果，但不确定这个查询是否正确，会不会在未来出现随机结果或数据错误，想请人验证它的正确性和稳定性。

示例查询代码：

SELECT T2.search_id    , T2.user_id    , T2.activity
     , T2.activity_date, T2.message    , T2.message_title
     , T1.github_id    , T1.search_date, T1.followers
     , T1.location     , T1.following  , T1.createdAt
     , T1.language     , T1.updatedAt
FROM (SELECT `github_id`, `search_date`, `followers`,
             `location` , `following`  , `createdAt`,
             `language` , `updatedAt`  , email 
      FROM DATA) as T1
LEFT JOIN (SELECT ,user_id, activity     , activity_date
                 , message, message_title, search_id
           from MESSAGES
           where user_id = 1 
           order by activity_date desc) as T2 
       on T1.github_id = T2.search_id 
group by T1.github_id,
         T2.search_id

注：示例查询可正常运行，忽略其中可能存在的语法错误，仅用于说明需求。

结论：原查询不稳定，存在数据错误风险 #

原查询的写法不符合SQL标准，后续必然会出现随机结果或数据错误，原因如下：

• 非聚合字段未在GROUP BY中声明：你仅在GROUP BY里指定了T1.github_id和T2.search_id，但SELECT中包含了大量未做聚合处理的字段（比如T2.activity、T2.activity_date等）。这种写法在部分数据库（如MySQL非严格模式）下会随机返回分组内某条记录的字段值，当前碰巧得到正确结果只是巧合，数据更新后一定会出错。
• 子查询排序无效：关联子查询中的ORDER BY activity_date desc不会被数据库保留，因为关联表的排序逻辑不会影响最终的关联结果，这个排序等于白写。

正确的稳定写法 #

方式1：用窗口函数（推荐，兼容MySQL8+、PostgreSQL等主流数据库） #

利用ROW_NUMBER()窗口函数给每个关联分组的记录按时间倒序编号，只取编号为1的最新记录：

SELECT 
    T2.search_id, T2.user_id, T2.activity,
    T2.activity_date, T2.message, T2.message_title,
    T1.github_id, T1.search_date, T1.followers,
    T1.location, T1.following, T1.createdAt,
    T1.language, T1.updatedAt
FROM DATA T1
LEFT JOIN (
    SELECT 
        user_id, activity, activity_date,
        message, message_title, search_id,
        -- 按search_id分组，按activity_date倒序生成行号
        ROW_NUMBER() OVER (PARTITION BY search_id ORDER BY activity_date DESC) AS rn
    FROM MESSAGES
    WHERE user_id = 1
) T2 ON T1.github_id = T2.search_id AND T2.rn = 1

这个写法逻辑清晰，能稳定获取每个search_id对应的最新记录，不会出现随机结果。

方式2：兼容旧版本数据库（如MySQL5.x） #

通过子查询获取每个关联项的最新日期，再关联到对应记录：

SELECT 
    T2.search_id, T2.user_id, T2.activity,
    T2.activity_date, T2.message, T2.message_title,
    T1.github_id, T1.search_date, T1.followers,
    T1.location, T1.following, T1.createdAt,
    T1.language, T1.updatedAt
FROM DATA T1
LEFT JOIN MESSAGES T2 
    ON T1.github_id = T2.search_id 
    AND T2.user_id = 1
    AND T2.activity_date = (
        -- 取当前search_id的最新activity_date
        SELECT MAX(activity_date) 
        FROM MESSAGES 
        WHERE search_id = T2.search_id AND user_id = 1
    )

注意：如果同一search_id和activity_date存在多条记录，会返回多条结果，此时需要添加额外的唯一条件（比如主键字段）来确保只取单条记录。

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