如何从MySQL中获取每个ID对应的最新行数据？

问题背景 #

你拥有的数据表内容如下：

ID    Date                 Value
AL-1  2017-04-01 22:04:08  154
AL-1  2017-04-05 15:08:45  158
AL-1  2017-04-06 18:09:15  225
AL-1  2017-04-08 20:08:17  254
AL-2  2017-04-01 22:04:08  154

你尝试执行以下SQL语句来获取每个ID的最新日期对应的记录：

select ID, Value from table1 where id in ('AL-1','AL-2') and group by max(date(Date));

但得到的输出不符合预期：

ID    Date                 Value
AL-1  2017-04-01 22:04:08  154
AL-2  2017-04-01 22:04:08  154

你期望的正确输出是：

ID    Date                 Value
AL-1  2017-04-08 20:08:17  254
AL-2  2017-04-01 22:04:08  154

原SQL的问题分析 #

你的SQL语句存在两处关键问题：

1. 语法错误：WHERE子句后直接跟and group by是完全错误的语法，而且GROUP BY的参数必须是SELECT中未被聚合的字段，用聚合函数max(date(Date))作为GROUP BY的分组依据不符合SQL规范。
2. 逻辑错误：这条语句无法建立“每个ID的最新日期”与对应Value的关联，自然无法筛选出每个ID最新日期的那条记录。

正确的解决方案 #

这里提供两种可靠的实现方式，适配不同的数据库版本：

方法1：使用窗口函数（推荐，支持MySQL 8+、PostgreSQL、SQL Server等）

利用ROW_NUMBER()窗口函数按ID分组，对日期进行降序排序，然后筛选出每组的第一条记录（即最新日期的记录）：

SELECT ID, Date, Value
FROM (
    SELECT 
        ID, 
        Date, 
        Value,
        -- 按ID分组，日期倒序排序，每组第一条标记为1
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date DESC) AS row_rank
    FROM table1
    WHERE ID IN ('AL-1','AL-2')
) ranked_data
WHERE row_rank = 1;

方法2：子查询关联（兼容旧版数据库）

先通过子查询获取每个ID的最大日期，再将该结果与原表关联，匹配出对应的记录：

SELECT t1.ID, t1.Date, t1.Value
FROM table1 t1
INNER JOIN (
    -- 先获取每个ID的最新日期
    SELECT ID, MAX(Date) AS latest_date
    FROM table1
    WHERE ID IN ('AL-1','AL-2')
    GROUP BY ID
) t2 ON t1.ID = t2.ID AND t1.Date = t2.latest_date;

这两种方法都能精准得到你期望的输出结果。

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