如何按application_id分组获取checkpoint_id=260对应MAX(created_at)的数据

问题背景 #

你需要实现的需求是：按每个application_id分组，获取该分组下created_at最新的记录，并且仅保留checkpoint_id = 260的数据。

给定的数据样本：

期望结果：返回application_id为1和11、checkpoint_id=260的两条记录。

原查询的问题分析 #

你最初尝试的查询语句：

SELECT * FROM histories h 
WHERE `checkpoint_id` = 260 
AND created_at = ( SELECT MAX(`created_at`) FROM histories GROUP BY h.application_id, h.checkpoint_id )

执行后只返回1条记录，原因是子查询SELECT MAX(created_at) FROM histories GROUP BY h.application_id, h.checkpoint_id会返回多个最大值（每个分组对应一个），而=运算符只能匹配单个值，因此只会命中其中一条记录，不符合预期。

可行的解决方案 #

方案1：你找到的关联子查询写法

这个写法通过先分组得到每个application_id和checkpoint_id的组合记录，再筛选checkpoint_id=260，最后关联子查询找到对应application_id的全局最大created_at，确保取到的是该application_id下最新且符合checkpoint_id要求的记录：

SELECT * FROM ( 
    SELECT application_id, checkpoint_id, created_at 
    FROM histories 
    GROUP BY application_id, checkpoint_id 
) AS h 
WHERE h.checkpoint_id = 260 
AND h.created_at=( SELECT MAX(`created_at`) FROM histories h2 WHERE h2.application_id=h.application_id )

方案2：使用窗口函数（更直观高效）

在支持窗口函数的数据库（比如MySQL 8.0+、PostgreSQL等）中，用ROW_NUMBER()窗口函数可以更清晰地实现需求，逻辑也更易懂：

SELECT application_id, checkpoint_id, created_at
FROM (
    SELECT 
        application_id, 
        checkpoint_id, 
        created_at,
        -- 按application_id分组，组内按created_at降序排序，给每条记录标序号
        ROW_NUMBER() OVER (PARTITION BY application_id ORDER BY created_at DESC) AS rn
    FROM histories
    WHERE checkpoint_id = 260 -- 先筛选符合checkpoint_id要求的数据
) t
WHERE rn = 1; -- 取每个分组的第一条（最新的）记录

这个方法先过滤出checkpoint_id=260的数据，再对每个application_id分组并按时间倒序，最后取每个组的第一条记录，完美匹配你的需求，性能也更优。

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