MySQL分组取最新一条详解|极客笔记

MySQL分组取最新一条详解

1. 前言

在实际业务中，我们经常会遇到需要对数据进行分组并取出每组中最新一条数据的情况。例如，我们有一个订单表，每个订单对应一个用户，用户可以多次下单。现在，我们希望按照用户分组，取出每个用户最新的一条订单记录。本文将以这个实际案例为例，详细解析在MySQL中如何使用分组查询取最新一条记录的方法。

2. 数据准备

为了方便演示，我们首先创建一个名为orders的订单表，并插入一些测试数据。

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  order_date DATE,
  order_amount DECIMAL(10,2)
);

INSERT INTO orders (user_id, order_date, order_amount) VALUES
  (1, '2022-04-01', 100.00),
  (1, '2022-04-02', 200.00),
  (2, '2022-04-01', 150.00),
  (2, '2022-04-03', 300.00),
  (2, '2022-04-04', 250.00),
  (3, '2022-04-02', 180.00),
  (3, '2022-04-03', 220.00);

以上代码创建了一个包含id、user_id、order_date和order_amount四个字段的表，并插入了7条订单记录。

3. 方法一：子查询 + MAX函数

我们可以使用子查询和MAX函数的组合来实现按照用户分组取最新一条记录的效果。具体步骤如下：

使用子查询得到每个用户的最新订单日期。
将该子查询结果与原始表进行连接，并按照用户和订单日期进行排序。
使用GROUP BY子句按照用户分组，并通过MAX函数获取每组中最新日期的订单id。
将以上结果与原始表连接，通过订单id来获取每个用户最新一条订单的详细信息。

下面是示例代码：

SELECT o.id, o.user_id, o.order_date, o.order_amount
FROM orders o
INNER JOIN (
  SELECT user_id, MAX(order_date) AS max_order_date
  FROM orders
  GROUP BY user_id
) t ON o.user_id = t.user_id AND o.order_date = t.max_order_date;

运行以上代码后，你将得到以下结果：

id	user_id	order_date	order_amount
2	1	2022-04-02	200.00
5	2	2022-04-04	250.00
7	3	2022-04-03	220.00

以上结果即为每个用户的最新订单记录。

4. 方法二：窗口函数

MySQL 8.0版本引入了窗口函数（Window Functions），通过窗口函数的使用可以更简洁地实现分组取最新一条记录的需求。具体步骤如下：

使用ROW_NUMBER()函数为每个用户的订单记录按日期进行排序，并为每条记录添加行号。
使用PARTITION BY子句将数据按照用户分组。
在外部查询中，使用WHERE子句保留每组中行号为1的记录，即最新的记录。

以下是示例代码：

SELECT id, user_id, order_date, order_amount
FROM (
  SELECT id, user_id, order_date, order_amount,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
  FROM orders
) t
WHERE rn = 1;

运行以上代码后，你将得到以下结果：

id	user_id	order_date	order_amount
2	1	2022-04-02	200.00
5	2	2022-04-04	250.00
7	3	2022-04-03	220.00

5. 方法三：自连接

除了使用子查询和窗口函数，我们还可以通过自连接的方式来实现分组取最新一条记录的需求。具体步骤如下：

通过自连接得到每个用户最新的订单日期。
将该结果与原始表进行连接，通过用户和订单日期联合匹配。
过滤掉连接结果中日期较大的记录。

以下是示例代码：

SELECT o1.id, 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.id IS NULL;

运行以上代码后，你将得到以下结果：

id	user_id	order_date	order_amount
2	1	2022-04-02	200.00
5	2	2022-04-04	250.00
7	3	2022-04-03	220.00

6. 方法四：GROUP BY + MAX函数

除了使用子查询和连接操作，我们还可以使用GROUP BY子句结合MAX函数来实现分组取最新一条记录的目标。具体步骤如下：

使用GROUP BY子句按照用户分组。
嵌套使用子查询，在子查询中使用MAX函数获取每组中最大的订单日期。
将以上结果与原始表进行连接，通过用户和订单日期进行联合匹配。

以下是示例代码：

SELECT o.id, o.user_id, o.order_date, o.order_amount
FROM orders o
INNER JOIN (
  SELECT user_id, MAX(order_date) AS max_order_date
  FROM orders
  GROUP BY user_id
) t ON o.user_id = t.user_id AND o.order_date = t.max_order_date;

运行以上代码后，你将得到以下结果：