用 SQL 窗口函数解决“每组取最新一条”

「每个用户取最新一条订单」是面试题，也是线上天天要写的查询。它看着简单，但 GROUP BY 配 MAX(created_at) 会取到错的行，JOIN 回原表遇到同一秒的两条记录会变成两行。

窗口函数是这个问题最稳的解法，前提是把排序写完整。下面用一张订单表把几种写法都跑一遍。

先看错误写法长什么样#

假设表结构是 orders(id, user_id, amount, status, created_at)，目标是每个用户一行，取未取消订单里最新的一条。

最常见的写法是 SELECT user_id, MAX(created_at) AS latest FROM orders GROUP BY user_id。这样只能拿到时间，拿不到那一行的 amount 和 status。于是有人补上 amount：在 ONLY_FULL_GROUP_BY 模式下直接报错；在关闭该模式的 MySQL 或某些 SQLite 版本里，它会返回「组内任意一行」的值——时间是最新的，金额可能来自三年前，而且不报错。这是最危险的一种：结果错了，但看起来像对的。

第二种错法是 GROUP BY 之后再 JOIN 回原表：

SELECT o.*
FROM orders o
JOIN (SELECT user_id, MAX(created_at) AS t FROM orders GROUP BY user_id) m
  ON m.user_id = o.user_id AND m.t = o.created_at;

同一用户在同一秒下了两单，就会返回两行；而且原表被扫了两遍。

ONLY_FULL_GROUP_BY 是 MySQL 5.7 起默认开启的 SQL 模式，它要求 SELECT 里的非聚合列必须出现在 GROUP BY 中。这不是刁难，而是在阻止「随便挑一行」的不确定行为。关掉它能让旧代码跑起来，但代价是查询结果依赖存储引擎的实现细节，升级或换引擎时可能悄悄改变。

ROW_NUMBER：先编号，再取第一#

先准备数据。时间用 ISO8601 字符串，比较结果和真实时间一致；生产上更推荐 timestamptz 或毫秒时间戳。

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    user_id    INTEGER NOT NULL,
    amount     INTEGER NOT NULL,
    status     TEXT    NOT NULL DEFAULT 'paid',
    created_at TEXT    NOT NULL
);

INSERT INTO orders (id, user_id, amount, status, created_at) VALUES
    (1, 1, 12800, 'paid',      '2026-01-03T09:00:00'),
    (2, 1,  9900, 'paid',      '2026-02-11T18:20:00'),
    (3, 1,  9900, 'refunded',  '2026-02-11T18:20:00'),  -- 与 2 同一时刻
    (4, 2, 45600, 'paid',      '2026-01-20T10:05:00'),
    (5, 2, 12000, 'cancelled', '2026-03-01T08:00:00'),
    (6, 3,  3000, 'paid',      '2026-02-28T23:59:59');

CREATE INDEX idx_orders_user_latest
    ON orders (user_id, created_at DESC, id DESC);

WITH ranked AS (
    SELECT id, user_id, amount, status, created_at,
           ROW_NUMBER() OVER (
               PARTITION BY user_id
               ORDER BY created_at DESC, id DESC
           ) AS rn
    FROM orders
    WHERE status <> 'cancelled'
)
SELECT id, user_id, amount, status, created_at
FROM ranked
WHERE rn = 1
ORDER BY user_id;

三点必须理解：

• 窗口函数在 WHERE、GROUP BY 之后才计算，所以不能在同一层写 WHERE rn = 1，必须套一层 CTE 或子查询。
• ORDER BY 要写完整。只按 created_at DESC 排序，同一秒的两条记录谁排第一由执行计划决定，今天跑和明天跑可能不同。加上 id DESC 做兜底，结果才稳定可复现。
• ROW_NUMBER 一定给唯一编号。如果业务上「并列最新」的两条都要，用 RANK() 再筛 rn = 1，会返回多行；ROW_NUMBER 只会保留一条。

在 SQLite 里跑上面的语句，结果是 (3, 1, 9900, refunded)、(4, 2, 45600, paid)、(6, 3, 3000, paid)。用户 2 的 cancelled 订单虽然更新，但在窗口计算前已被过滤，符合预期。

并列、NULL 与跨库差异#

两个细节：

created_at 可能为 NULL。PostgreSQL 在 DESC 时默认 NULLS FIRST，MySQL 反过来，DESC 时 NULL 排在最后。同一条 SQL 换个库，结果可能完全不同。要么建表时 NOT NULL，要么显式写 ORDER BY created_at DESC NULLS LAST（MySQL 不支持这个语法，用 ORDER BY created_at IS NULL, created_at DESC 代替）。

DISTINCT ON 的排序要和它对齐。PostgreSQL 的写法是 SELECT DISTINCT ON (user_id) ... ORDER BY user_id, created_at DESC, id DESC，第一列必须是 user_id，否则报错。它比窗口函数短，但只此一家，换库就得重写。

性能：索引比写法更重要#

窗口函数要对每个分区排序。如果 (user_id, created_at DESC, id DESC) 上有索引，数据库可以直接按顺序读，省掉排序；没有索引，就是全表扫加临时 B 树排序，百万行级别会明显变慢。

看执行计划确认：SQLite 用 EXPLAIN QUERY PLAN，PostgreSQL 用 EXPLAIN (ANALYZE, BUFFERS)。如果窗口那一步仍然出现 USE TEMP B-TREE FOR ORDER BY，说明索引没覆盖分区排序；最外层的 ORDER BY user_id 另算，通常只排几十行，影响不大。

排序本身也有成本上限。排序数据超过 work_mem（PostgreSQL）或 sort_buffer_size（MySQL）时会落到磁盘，查询从几十毫秒变成几秒。如果每个用户只有几十单，分区排序的内存压力不大；如果某个用户有几百万单，那一个分区就能把临时空间打满——这类「超级用户」在生产上真实存在，值得先按 user_id 查一次最大分组。窗口函数不可用的老版本（如 MySQL 5.7）只能用相关子查询，写法见下一节。

另外，能提前过滤的条件一定放进 CTE 里，先缩小数据集再开窗。如果过滤后仍要排序几十万行，说明「每人一条」这个需求本身就该配一张宽表，或者定时预计算，而不是每次查询都现算。

如果表很大而用户很少，相关子查询配合索引有时更快：它按 user_id 逐行走索引取一条，不用对全表排序。反过来，用户量几十万、每人只有几单时，它可能变成几十万次索引查找。两种写法都该在真实数据量上 EXPLAIN 一次再定。

替代写法不是不能用#

相关子查询的可读性其实最好：

SELECT o.id, o.user_id, o.amount, o.status, o.created_at
FROM orders o
WHERE o.status <> 'cancelled'
  AND o.id = (
      SELECT o2.id
      FROM orders o2
      WHERE o2.user_id = o.user_id
        AND o2.status <> 'cancelled'
      ORDER BY o2.created_at DESC, o2.id DESC
      LIMIT 1
  )
ORDER BY o.user_id;

它和窗口函数的结果一致，但依赖 (user_id, created_at DESC) 上的索引。没有索引时，外层每一行都要扫一遍子查询，复杂度是平方级的。数据量小的时候无所谓，涨到百万行就会成为事故。

结论#

• ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 加外层 rn = 1 是最通用、最不容易错的写法。
• 排序必须补一个唯一列（如 id）做 tie-breaker，否则结果不可复现。
• 过滤条件放在窗口计算之前，别让取消的订单参与排名。
• 建 (user_id, created_at DESC, id DESC) 索引，否则窗口排序会成为瓶颈。
• NULL 的排序方向各库不同，建表时 NOT NULL 或显式声明 NULLS LAST。
• 没有窗口函数的旧版本，用相关子查询配 (user_id, created_at DESC) 索引，别用 GROUP BY + JOIN。