SQL中如何查询每个分组的最新一条记录-SQL-PHP中文网

ROW_NUMBER()按PARTITION BY分组、ORDER BY时间倒序编号并筛选rn=1是最通用方案；须避免漏写ORDER BY或误用ASC，重复时间需补主键确保确定性；老版MySQL用JOIN子查询，PG可用DISTINCT ON；索引应建(user_id, created_at DESC, id DESC)。

sql中如何查询每个分组的最新一条记录

用窗口函数 `ROW_NUMBER()` 按时间倒序取首行

这是最通用、可读性最强的方案，适用于 PostgreSQL、SQL Server、MySQL 8.0+、Oracle 等支持窗口函数的数据库。核心思路是：先按分组字段（如 user_id）分区，再按时间字段（如 created_at）降序排序，最后筛选每组序号为 1 的记录。

常见错误是误用 ORDER BY created_at ASC —— 这会拿到“最早”而非“最新”。另外，如果时间字段有重复值，仅靠 created_at 排序可能产生不确定结果，建议补充主键或唯一字段作为第二排序条件（如 ORDER BY created_at DESC, id DESC）。

示例（查每个用户的最新订单）：

SELECT user_id, order_id, amount, created_at
FROM (
  SELECT user_id, order_id, amount, created_at,
         ROW_NUMBER() OVER (
           PARTITION BY user_id 
           ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM orders
) t
WHERE rn = 1;

MySQL 5.7 及更早版本只能用 `JOIN` + 子查询

老版本 MySQL 不支持窗口函数，也不能在 WHERE 中直接引用聚合结果。典型做法是先用子查询找出每组最大时间戳，再和原表 JOIN 匹配。但要注意：若同一用户有多条记录时间戳完全相同，JOIN 会返回多行 —— 这不是 bug，而是语义使然。

避免漏数据的关键点：

子查询必须只返回 GROUP BY 字段 + 聚合字段（如 MAX(created_at)），不能带其他列
JOIN 条件要同时匹配分组字段和时间字段，缺一不可
如果业务上要求“唯一一条”，需额外加 id = MAX(id) 或用 LIMIT 1 配合 ORDER BY（但后者无法在子查询中直接使用）

示例：

SELECT o1.user_id, o1.order_id, o1.amount, o1.created_at
FROM orders o1
INNER JOIN (
  SELECT user_id, MAX(created_at) AS max_time
  FROM orders
  GROUP BY user_id
) o2 ON o1.user_id = o2.user_id AND o1.created_at = o2.max_time;

PostgreSQL 中 `DISTINCT ON` 更简洁但不跨平台

DISTINCT ON 是 PostgreSQL 特有语法，写起来短，执行效率通常也不错，但它只存在于 PG，迁移到其他数据库时必须重写。它本质是“对指定列组合取第一条”，依赖外层 ORDER BY 的顺序。

容易踩的坑：

DISTINCT ON 后的字段必须出现在 ORDER BY 开头，否则报错 ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
如果 ORDER BY 没写全排序条件，结果可能不稳定（比如只写 created_at DESC，没加 id DESC）
不能和普通 DISTINCT 混用，也不能出现在子查询的 SELECT 列表里（除非整个查询是子查询）

示例：

SELECT DISTINCT ON (user_id)
       user_id, order_id, amount, created_at
FROM orders
ORDER BY user_id, created_at DESC, id DESC;

性能与索引怎么配才不慢

无论用哪种写法，没有合适索引时，大数据量下基本就是全表扫描。关键索引不是简单建在时间字段上，而是按查询模式组合：

对 ROW_NUMBER() 方案：建复合索引 (user_id, created_at DESC, id DESC) —— 分区字段放最左，排序字段紧随其后
对 JOIN 子查询方案：索引 (user_id, created_at) 能加速子查询的 GROUP BY 和 MAX()；主表 JOIN 时，(user_id, created_at, id) 能覆盖查询所需全部字段，避免回表
别建单列索引 created_at —— 它对分组取最新毫无帮助，反而增加写入开销