MySQL中按(user,a,b)分组获取每组最新行数据

没问题，我来帮你搞定这个SQL查询的问题～

问题背景 #

我有一张名为foo的表，表结构如下：

+----------+--------------+------+-----+-------------------+----------------+
| Field    | Type         | Null | Key | Default           | Extra          |
+----------+--------------+------+-----+-------------------+----------------+
| id       | int(10)      | NO   | PRI | NULL              | auto_increment |
| ts       | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| user     | varchar(255) | YES  | MUL | NULL              |                |
| a        | varchar(255) | YES  | MUL | NULL              |                |
| b        | varchar(255) | YES  | MUL | NULL              |                |
| c        | float        | YES  |     | NULL              |                |
+----------+--------------+------+-----+-------------------+----------------+

需求：针对每一组不同的(user,a,b)，选取最新的timestamp（ts）以及对应的c值。

我尝试了如下SQL语句的多种变体，但均未成功：

select distinct user, b, c, ts as ts_b from (select max(ts) as ts_a from foo as max_ts) where ts_a = ts_b;

解决方案 #

你的原始SQL逻辑存在两个关键问题：一是子查询没有按(user,a,b)分组，只算了全表的最大时间戳；二是子查询和主表没有关联关系，根本无法匹配到对应分组的最新记录。下面给你几种可行的解决方法：

方法1：窗口函数（MySQL 8.0+ 首推）

如果你的MySQL版本是8.0及以上，用ROW_NUMBER()窗口函数是最直观高效的方式：

SELECT user, a, b, ts, c
FROM (
    SELECT 
        user, a, b, ts, c,
        -- 按user,a,b分组，每组内按ts从新到旧排序，给每条记录打序号
        ROW_NUMBER() OVER (PARTITION BY user, a, b ORDER BY ts DESC) AS rn
    FROM foo
) t
-- 只取每组的第一条（序号为1的就是最新记录）
WHERE rn = 1;

方法2：关联子查询（兼容低版本MySQL）

要是你用的是低于8.0的MySQL，不支持窗口函数，可以先分组拿到每个(user,a,b)的最新时间戳，再关联原表获取对应的c值：

SELECT f.user, f.a, f.b, f.ts, f.c
FROM foo f
INNER JOIN (
    -- 先按user,a,b分组，计算每组的最大ts
    SELECT user, a, b, MAX(ts) AS max_ts
    FROM foo
    GROUP BY user, a, b
) t 
-- 通过user,a,b和ts关联，拿到对应记录的c值
ON f.user = t.user AND f.a = t.a AND f.b = t.b AND f.ts = t.max_ts;

方法3：子查询匹配（简洁写法）

还有一种更简洁的写法，直接在WHERE子句中匹配同分组的最大时间戳：

SELECT f.user, f.a, f.b, f.ts, f.c
FROM foo f
WHERE f.ts = (
    -- 找到当前记录同user,a,b分组下的最大ts
    SELECT MAX(ts)
    FROM foo
    WHERE user = f.user AND a = f.a AND b = f.b
);

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