MySQL 派生表怎样为每组取前几条记录

MySQL 要“每个客户取最近 3 笔订单”，关键不是在一个查询层里直接给窗口函数写 WHERE rn ，而是先在派生表中完成分组编号，再由外层查询过滤编号。ROW_NUMBER() 负责按客户分组、按时间排序，派生表则把这个计算结果变成可过滤的列。

官方资料：https://dev.mysql.com/doc/refman/8.4/en/

先把“每组”定义成稳定的排序范围

假设订单表为 orders，字段包含 customer_id、created_at、id 和 amount。取每位客户最近三笔时，时间相同的记录必须再用唯一键打破平局，否则同一批数据的名次可能不稳定。

SELECT
    customer_id,
    id,
    created_at,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY created_at DESC, id DESC
    ) AS rn -- 先按客户分组，再用时间和主键稳定编号
FROM orders;

ROW_NUMBER() 会在每个分区内从 1 编号。这里的 id DESC 不是为了改变业务排序，而是让同一时间的订单仍有确定顺序；如果业务没有这样的次级键，应先明确“并列时任选”还是“并列全部保留”。

用派生表把窗口结果变成可过滤列

窗口函数的结果要在下一层使用，因此把上面的查询包成派生表，并为它设置别名。外层再筛选 rn，就能得到每个客户的前 3 条订单。

SELECT
    customer_id,
    id,
    created_at,
    amount
FROM (
    SELECT
        customer_id,
        id,
        created_at,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY created_at DESC, id DESC
        ) AS rn -- 在内层完成每组排序和编号
    FROM orders
) AS ranked_orders -- MySQL 要求派生表具有表别名
WHERE rn 

这段写法只围绕窗口函数和派生表展开：内层负责“算名次”，外层负责“选结果”。不要把 rn 写回窗口计算所在的同一层；那一层的 WHERE 还看不到窗口函数产生的列。

如果前几条数量来自应用参数，先把参数绑定为整数并限制合理范围，再替换示例中的 3。不要把未经约束的字符串直接拼到 SQL 中。

并列记录到底保留几行

“前 3 名”与“前 3 行”不是同一件事。选择窗口函数时先确定并列规则：

SELECT customer_id, id, amount
FROM (
    SELECT
        customer_id,
        id,
        amount,
        DENSE_RANK() OVER (
            PARTITION BY customer_id
            ORDER BY amount DESC
        ) AS price_rank -- 相同金额共享一个层级
    FROM orders
) AS ranked_orders
WHERE price_rank 

如果必须严格返回每组最多三行，使用 ROW_NUMBER() 并补充唯一排序键；如果同金额订单都必须展示，就使用排名函数并在接口说明中写清“行数可能超过 N”。

把索引和边界检查补上

对大表不要只看 SQL 是否返回正确结果。可以先为常用分组和排序建立复合索引，例如 (customer_id, created_at, id)，再使用 EXPLAIN 观察访问路径；窗口计算仍可能需要排序或临时空间，索引不能直接保证固定执行计划。

EXPLAIN
SELECT customer_id, id, created_at, amount
FROM (
    SELECT
        customer_id, id, created_at, amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY created_at DESC, id DESC
        ) AS rn -- 检查分组与排序的实际代价
    FROM orders
) AS ranked_orders
WHERE rn 

回归数据至少包含：某客户只有一条记录、同一时间有多条记录、金额并列、时间为 NULL、没有任何订单，以及前 N 大于组内行数。特别要确认 NULL 的排序方向符合业务预期，不要把数据库默认顺序当成业务规则。

常见问题

为什么不能直接在同一层写 WHERE rn

因为 rn 是窗口计算产生的结果列，当前查询层的 WHERE 不能直接引用它。派生表把计算结果交给外层后，外层才可以过滤。

ROW_NUMBER() 会不会把并列记录漏掉？

会，它按排序键给每行唯一编号。若业务要求并列全部保留，改用 RANK() 或 DENSE_RANK()，并接受每组返回行数超过 N。

派生表一定比相关子查询快吗？

不能这样概括。派生表让语义和过滤层清晰，但实际代价仍取决于数据量、排序、索引和执行计划，应以目标数据规模下的 EXPLAIN 结果判断。

这类查询可以记成两层：内层用窗口函数回答“每组第几条”，外层用派生表回答“保留哪些名次”。先固定并列规则，再补充稳定排序键和边界测试，Top N 结果才不会只在样例数据上正确。