本文介绍在 MySQL 中使用窗口函数 ROW_NUMBER() 按 study_id 分组，并基于时间字段（如 created_at 或 last_completion_date）筛选每组最新 2 条记录的完整方案，包含可执行 SQL 示例、关键注意事项及常见错误规避方法。
本文介绍在 mysql 中使用窗口函数 `row_number()` 按 `study_id` 分组，并基于时间字段（如 `created_at` 或 `last_completion_date`）筛选每组最新 2 条记录的完整方案，包含可执行 sql 示例、关键注意事项及常见错误规避方法。
在数据分析与调度监控场景中，常需从历史日志表（如 analytics_cron_refresh_time）中提取每个研究项目（study_id）最近的若干次成功执行记录。原始需求是：对每个 study_id，仅返回状态为 'Complete' 的最新 2 条记录。这属于典型的“分组取 Top-N”问题，传统自连接或子查询易出错且性能差，而现代 MySQL（8.0+）推荐使用窗口函数高效解决。
以下为推荐的标准化写法（以 created_at 为准，更符合“最近插入”的业务语义）：
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'
) ranked
WHERE rn <= 2
ORDER BY study_id, created_at DESC;✅ 关键说明：
-
PARTITION BY study_id实现按研究 ID 分组； -
ORDER BY created_at DESC确保时间倒序，最新记录排第一； -
ROW_NUMBER()为每组内记录严格编号（无重复、不跳号），rn 精确截取前两条； - 外层
ORDER BY便于结果可读性，不影响逻辑。
⚠️ 注意事项：
-
MySQL 版本要求：
ROW_NUMBER()自 MySQL 8.0 起支持，若使用 5.7 或更低版本，需改用变量模拟或自连接（性能显著下降，不推荐）； -
索引优化：建议为
(study_id, status, created_at)建立联合索引，加速WHERE status = 'Complete'+ 分组排序；当前已有idx_study_id_status，但顺序为(status, study_id, api_cron_job_id)，建议调整为(study_id, status, created_at)； -
时间精度：表中
created_at类型为datetime(3)（毫秒级），ORDER BY可精确到毫秒，避免因时间相同导致排序不确定性；若存在完全相同时间戳，可追加id或api_cron_job_id作为次要排序键确保稳定性； -
原查询错误分析：提问中使用的自连接 +
GROUP BY方式逻辑混乱——LEFT JOIN条件缺失t2.status = 'Complete'，HAVING COUNT(*) > 2实际过滤的是“有超过2条更晚记录”的行（即倒数第3条及以后），且未限定t1.status在连接前已过滤，导致结果不可控。
? 扩展建议：
若需同时获取“最新完成时间”和“上一次完成时间”作为独立字段（如用于对比分析），可改用 LAG() 函数；若需兼容 In Progress 状态的最近记录，则移除 WHERE status = 'Complete' 并在业务层后处理。
掌握此模式后，可轻松适配任意“每组取 N 条”的场景（如 Top 3、最新 1 条等），大幅提升复杂查询的可维护性与执行效率。
