MySQL 窗口函数取每组最新记录时怎样稳定排序

在订单、设备、用户等分组数据里，“每组最新一条”看起来只是排序后取第一行，但真正上线后最容易出现两个问题：同一组有相同时间戳时，结果偶尔换人；要取每组前 3 条时，并列记录又不知道应该严格保留 3 行，还是把并列项一起带上。稳定写法是把窗口定义拆成三部分：PARTITION BY 划分分组，窗口内 ORDER BY 定义组内顺序，再用唯一键完成最后的平局裁决。

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

如果题目是“每组固定取几行”，优先使用 ROW_NUMBER()；如果并列记录必须同进同出，再考虑 RANK() 或 DENSE_RANK()。时间字段不是唯一排序键时，要在窗口排序末尾补上主键或其他唯一列。

先把“最新”写成完整排序规则

假设有一张订单状态表，每次状态变化都会产生一行：

CREATE TABLE order_status_log (
    id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    status VARCHAR(32) NOT NULL,
    changed_at DATETIME(6) NOT NULL,
    operator_id BIGINT NULL
);

-- 先按订单分组，再按变更时间倒序；id 用来处理同一时刻的并列
CREATE INDEX idx_order_status_latest
    ON order_status_log (order_id, changed_at DESC, id DESC);

这里的“最新”并不是单独的 changed_at DESC，而是一个有优先级的排序元组：先比较时间，时间相同再比较 id。如果业务规定时间相同的记录按导入批次、版本号或状态序号决定，就把那个字段放在唯一键之前。关键是最终必须有一个能区分行的稳定字段，否则 SQL 只能知道两行属于同一并列组，不能凭空决定谁排第一。

ROW_NUMBER 怎样稳定取每组最新一条

窗口函数不会把分组压成一行，而是为每一行计算组内结果。PARTITION BY order_id 把订单状态分成多个窗口，ORDER BY changed_at DESC, id DESC 决定每个窗口的顺序，外层查询再保留编号为 1 的行：

WITH ranked AS (
    SELECT
        id,
        order_id,
        status,
        changed_at,
        operator_id,
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY changed_at DESC, id DESC
        ) AS rn
    FROM order_status_log
)
SELECT id, order_id, status, changed_at, operator_id
FROM ranked
WHERE rn = 1
ORDER BY order_id;

这条语句的稳定性来自两个地方：第一，窗口排序写在 OVER 内，而不是只在查询末尾写一个全局排序；第二，时间相同的行由 id DESC 裁决。最后的 ORDER BY order_id 只负责结果集展示顺序，它不会替代窗口内部的排序。

每组前 N 条时，三种排名函数怎样选

“前 3 条”可能有两种不同含义：一种是每组严格返回 3 行，另一种是第 3 名并列的记录也要全部返回。三种函数的差异可以用同一组排序值理解：

例如每个店铺取销售额前 3 名，且销售额相同的商品必须同进同出，可以使用 RANK()：

WITH ranked AS (
    SELECT
        shop_id,
        product_id,
        sales_amount,
        sold_at,
        RANK() OVER (
            PARTITION BY shop_id
            ORDER BY sales_amount DESC
        ) AS sales_rank
    FROM shop_product_sales
)
SELECT shop_id, product_id, sales_amount, sold_at, sales_rank
FROM ranked
WHERE sales_rank 

如果要求“每组一定三行”，就改成 ROW_NUMBER()，并在窗口内加上确定性的平局键，例如 product_id DESC。如果要求的是前三个不同销售额等级，则用 DENSE_RANK()，但要先确认并列值的业务含义，而不是看到函数名相近就替换。

窗口结果为什么要放到外层再筛选

窗口函数发生在结果行形成之后，不能像普通列一样直接放进同一层的 WHERE。因此要先在 CTE 或派生表里计算排名，再由外层筛选：

SELECT order_id, status, changed_at
FROM (
    SELECT
        order_id,
        status,
        changed_at,
        id,
        -- 外层只需要判断 rn，内层负责完整排序
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY changed_at DESC, id DESC
        ) AS rn
    FROM order_status_log
) AS ranked
WHERE rn 

外层的排序也建议写清楚。否则即使每组编号已经稳定，最终返回的不同分组之间仍然没有展示顺序保证。对于分页接口，还应把外层排序和分页键设计成一致的稳定顺序，避免翻页时同一条记录在边界附近重复或消失。

用固定数据和 EXPLAIN 检查排序是否真的稳定

这类查询不需要靠“多跑几次看起来没变”来判断。准备至少包含同一分组相同时间、相同分值和不同唯一键的数据，逐项确认：

• PARTITION BY 只包含真正的分组字段，没有把状态或时间误放进去。
• 窗口 ORDER BY 的第一排序键符合“最新”或“最高”的业务定义。
• 末尾存在唯一键或等价的唯一组合，能处理所有并列。
• ROW_NUMBER 的过滤结果是每组固定行数；RANK 或 DENSE_RANK 的结果行数则按并列规则解释。
• 外层 ORDER BY 与接口需要的展示、分页顺序一致。

EXPLAIN
WITH ranked AS (
    SELECT
        order_id,
        status,
        changed_at,
        id,
        -- 计划检查关注分组、排序和外层过滤的代价
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY changed_at DESC, id DESC
        ) AS rn
    FROM order_status_log
)
SELECT order_id, status, changed_at
FROM ranked
WHERE rn = 1
ORDER BY order_id;

索引可以帮助缩小扫描范围，但不要把“有索引”误认为“结果自动稳定”。稳定性首先由窗口排序语义决定，执行计划则用来观察扫描、排序和数据量是否符合预期。表数据量大时，再结合实际版本、统计信息和执行计划调整索引，不要为了凑一个索引顺序改变业务排序。

最后记住这条选择规则

每组最新一条：ROW_NUMBER() OVER (PARTITION BY 分组 ORDER BY 时间 DESC, 唯一键 DESC)，外层取 rn = 1。每组严格前 N 行：使用 ROW_NUMBER()；并列必须全部保留：使用 RANK()；按不间断等级筛选：使用 DENSE_RANK()。任何情况下，都要把并列处理写成明确的业务决定，而不是依赖数据库在缺少完整排序键时的偶然输出。

相关问题

窗口函数中的 ORDER BY 和最终 ORDER BY 是一回事吗？
不是。前者决定每个分组内的排名，后者决定最终结果集的展示顺序，两者可以相同，也可以不同，但都应按需求明确写出。

只按时间排序为什么会出现最新记录变化？
当同一分组的多行时间相同时，它们属于并列行；如果没有唯一键作为后续排序条件，SQL 没有规定哪一行先出现。

ROW_NUMBER 能不能直接写在 WHERE 里？
不能在同一查询层直接这样筛选，应先用 CTE 或派生表计算编号，再由外层过滤。