如何为多个study_id查询各自最新的2条记录？

原SQL的问题分析 #

• JOIN条件中t1.status未指定具体逻辑，属于无效条件；
• GROUP BY t1.study_id配合SELECT t1.*不符合SQL标准（非聚合列未包含在GROUP BY中），且只能返回每个study_id的单条记录，无法满足取2条的需求；
• HAVING COUNT(*) > 2会过滤掉记录数≤2的study_id，与需求不符。

正确解法 #

方法1：使用窗口函数（MySQL 8.0及以上版本推荐）

利用ROW_NUMBER()窗口函数按study_id分组，按created_at倒序排序，直接筛选每组前2条记录：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY study_id ORDER BY created_at DESC) AS rn
    FROM analytics_cron_refresh_time
    WHERE status = 'Complete'
) t
WHERE rn <= 2
ORDER BY study_id, created_at DESC;

• PARTITION BY study_id：按study_id拆分分组
• ORDER BY created_at DESC：每组内按创建时间倒序排列，最新记录排首位
• rn <= 2：保留每组前2条最新记录

方法2：自连接计数（兼容低版本MySQL）

通过自连接统计同study_id、同状态下比当前记录新的数量，数量小于2的即为组内前2新的记录：

SELECT t1.*
FROM analytics_cron_refresh_time t1
LEFT JOIN analytics_cron_refresh_time t2 
    ON t1.study_id = t2.study_id 
    AND t1.status = t2.status
    AND t1.created_at < t2.created_at
WHERE t1.status = 'Complete'
GROUP BY t1.id, t1.study_id, t1.client_id, t1.last_completion_date, t1.status, t1.created_at, t1.modifed_at, t1.api_cron_job_id
HAVING COUNT(t2.id) < 2
ORDER BY t1.study_id, t1.created_at DESC;

• 自连接条件限定只统计同study_id、同Complete状态下，创建时间晚于当前记录的条目数
• COUNT(t2.id) < 2：表示当前记录是组内前2新的（0条比它新=最新，1条比它新=第二新）
• GROUP BY包含所有查询字段，符合SQL标准要求

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