MySQL分组后非分组列折叠：如何控制显示的目标行？

嗨，这个需求我在做用户订单、日志统计的时候经常碰到，下面给你分享几种实用的实现方式，分版本给你讲，方便你根据自己的MySQL版本选择：

一、MySQL 8.0+ 用窗口函数（最推荐） #

MySQL 8.0之后支持窗口函数，这是处理这类分组取Top N场景最直观、最灵活的方式。常用的窗口函数有ROW_NUMBER()、RANK()、DENSE_RANK()，根据你对并列值的处理需求选就行：

1. 取每组唯一的顶部行（忽略并列） #

比如我们有个orders表，要获取每个用户最新创建的订单（按order_date降序，即使有同一天的订单也只取其中一条）：

SELECT user_id, order_date, order_amount
FROM (
    SELECT 
        user_id, 
        order_date, 
        order_amount,
        -- 按user_id分组，组内按order_date降序编序号
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS row_num
    FROM orders
) AS ranked_orders
WHERE row_num = 1;

ROW_NUMBER()会给每组内的行按顺序分配唯一的序号，哪怕两行的order_date相同，序号也不一样，所以最终每组只会返回一行。

2. 保留并列的顶部行 #

如果想把同一用户下所有和最新日期相同的订单都返回，就用RANK()或者DENSE_RANK()替换ROW_NUMBER()：

SELECT user_id, order_date, order_amount
FROM (
    SELECT 
        user_id, 
        order_date, 
        order_amount,
        RANK() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS row_num
    FROM orders
) AS ranked_orders
WHERE row_num = 1;

比如同一个用户有两个订单在同一天，这两个订单的row_num都会是1，都会被筛选出来。

二、MySQL 5.7及以下版本（无窗口函数） #

如果你的项目还在用老版本MySQL，也有两种靠谱的方法：

1. 关联查询法 #

思路是：对每一行数据，找同组内比它排序更靠前的行，如果找不到，那它就是组内的顶部行。还是以取最新订单为例：

SELECT o1.user_id, o1.order_date, o1.order_amount
FROM orders o1
LEFT JOIN orders o2 
    ON o1.user_id = o2.user_id 
    AND o1.order_date < o2.order_date -- 找同用户下日期更晚的订单
WHERE o2.user_id IS NULL; -- 找不到的话，o1就是最新的

这个方法会自动保留并列的顶部行，比如同一用户同一天的订单都会被返回。

2. GROUP BY + 子查询关联 #

先通过GROUP BY获取每组的最大（或最小）排序值，再关联原表取出对应的数据：

SELECT o.user_id, o.order_date, o.order_amount
FROM orders o
INNER JOIN (
    -- 先拿到每个用户的最新订单日期
    SELECT user_id, MAX(order_date) AS max_date
    FROM orders
    GROUP BY user_id
) AS latest_dates 
    ON o.user_id = latest_dates.user_id 
    AND o.order_date = latest_dates.max_date;

这个方法同样会返回所有符合最大日期的订单，适合需要保留并列值的场景。

一些小提示 #

• 如果要取每组的最旧订单，只需要把排序方向改成ASC，或者把MAX()换成MIN()就行。
• 窗口函数还能轻松实现取每组前N行，比如把WHERE row_num = 1改成WHERE row_num <= 3就能取每个用户的最新3条订单。
• 老版本的方法记得给分组列和排序列加联合索引（比如INDEX idx_user_date (user_id, order_date)），不然数据量大的时候查询会很慢。

内容的提问来源于stack exchange，提问作者curiousgeorge