MySQL window function怎样在分组内取第二条记录

在订单、成绩或日志表中，常见需求不是取每组第一条，而是“每个客户的第二笔订单”“每个项目的第二条最新记录”。MySQL 的正确思路是先在组内排序并生成行号，再从外层查询筛选第二行。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/window-functions-usage.html

最通用的写法是：ROW_NUMBER() OVER (PARTITION BY 分组列 ORDER BY 排序列 DESC, 唯一列 DESC)，把结果放进派生表后再使用 WHERE rn = 2。

先把“第二条”定义成可排序的规则

“第二条”不能脱离排序规则单独存在。下面假设有一张 orders 表，需要按 customer_id 分组，取每个客户按下单时间倒序排列后的第二笔订单：

-- 示例数据：同一客户可能有多笔订单，order_id 用来补足并列时的顺序
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    created_at DATETIME NOT NULL,
    amount DECIMAL(12, 2) NOT NULL
);

-- 目标：每个客户按最新下单时间排序，取排序后的第二笔订单
SELECT order_id, customer_id, created_at, amount
FROM orders
ORDER BY customer_id, created_at DESC, order_id DESC;

这里至少要确定四件事：按哪个字段分组、按哪个字段排序、取最新还是最早、排序字段相同时如何打破平局。若 created_at 可能相同，追加唯一的 order_id 可以让每次编号更稳定。

用 ROW_NUMBER 在每个分组内编号

窗口函数不会把分组压缩成一行，而是为原来的每一行附加计算结果。PARTITION BY customer_id 表示每个客户重新编号，ORDER BY created_at DESC 表示最新记录排在前面：

-- 先在每个客户分组内生成从 1 开始的连续行号
SELECT
    order_id,
    customer_id,
    created_at,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY created_at DESC, order_id DESC
    ) AS rn
FROM orders;

假设客户 10 有三笔订单，生成的 rn 依次是 1、2、3；客户 20 会重新从 1 开始。此时还没有筛掉其它行，因为窗口函数的作用是给结果集中的每行加上编号。

在外层筛选 rn = 2

窗口函数的结果不能直接在同一层的 WHERE 中使用，因此要先放到派生表或 CTE 中，再由外层筛选：

-- 派生表先完成窗口计算，外层再筛选每组第二行
SELECT order_id, customer_id, created_at, amount
FROM (
    SELECT
        order_id,
        customer_id,
        created_at,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY created_at DESC, order_id DESC
        ) AS rn
    FROM orders
) AS ranked_orders
WHERE rn = 2
ORDER BY customer_id;

如果更喜欢把步骤拆开，也可以使用 CTE：

-- CTE 让“编号”和“筛选”两个阶段更容易阅读
WITH ranked_orders AS (
    SELECT
        order_id,
        customer_id,
        created_at,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY created_at DESC, order_id DESC
        ) AS rn
    FROM orders
)
SELECT order_id, customer_id, created_at, amount
FROM ranked_orders
WHERE rn = 2
ORDER BY customer_id;

某个客户只有一笔订单时，它只有 rn = 1，自然不会出现在结果中。某个客户没有任何订单，也不会凭空生成一行。

并列值时不要误用 ROW_NUMBER

如果业务把相同排序值视为同一名次，就要先决定“第二条”到底是第二个物理行，还是第二个不同名次。MySQL 文档把排序值相同的行视为 peers，三种常见函数的行为不同：

-- 只取每组第二个不同的金额档位，并保留该档位的所有订单
WITH ranked_orders AS (
    SELECT
        order_id,
        customer_id,
        created_at,
        amount,
        DENSE_RANK() OVER (
            PARTITION BY customer_id
            ORDER BY amount DESC
        ) AS amount_rank
    FROM orders
)
SELECT order_id, customer_id, created_at, amount
FROM ranked_orders
WHERE amount_rank = 2
ORDER BY customer_id, order_id;

如果只是想要每组排序后的第二个物理记录，继续使用 ROW_NUMBER()，并在 ORDER BY 中加入唯一列。若要保留所有并列第二名，改用 RANK() 或 DENSE_RANK()，选择哪一个取决于是否需要名次跳号。

稳定排序和性能边界

第一，窗口里的排序决定编号，最外层的 ORDER BY 只决定最终展示顺序，两者不要混为一谈。第二，排序列不唯一时，数据库可以在并列行之间给出不同的行号，所以需要追加主键或其它唯一列。第三，分组列和排序列上的索引有助于减少数据访问成本，但窗口排序仍可能带来排序和临时空间开销，大表应结合实际数据量观察执行计划。

-- 用 EXPLAIN 观察窗口查询的访问路径和排序相关信息
EXPLAIN
SELECT order_id, customer_id, created_at, amount
FROM (
    SELECT
        order_id,
        customer_id,
        created_at,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY created_at DESC, order_id DESC
        ) AS rn
    FROM orders
) AS ranked_orders
WHERE rn = 2;

不要为了“取第二条”把查询改写成没有明确排序的 LIMIT 1, 1。那种写法只能处理整个结果集的偏移，不能为每个分组分别编号。

一张表记住选择

把需求拆成“分组列 + 排序列 + 并列规则”后，MySQL window function 取第二条记录就变成了稳定的两阶段查询：窗口层负责编号，外层负责筛选。真正需要留意的不是把 rn = 2 写出来，而是确认这个 2 对应的是物理行还是业务名次。

相关问题

• 每组取前三条怎么写？ 把外层条件改为 rn ，仍然使用同一组内排序规则。
• 每组取最早一条怎么办？ 将窗口里的时间列改为 ASC，并保留唯一列作为平局排序。
• 并列第二名要全部保留怎么办？ 使用 RANK() 或 DENSE_RANK()，不要用会强制拆开并列行的 ROW_NUMBER()。