mysql 获取分组最新一条

原创

mob64ca12e04e7a 2024-01-22 03:53:37 ©著作权

文章标签 MySQL 子查询窗口函数 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者mob64ca12e04e7a的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL获取分组最新一条记录

在使用MySQL数据库时，经常会遇到需要获取每个分组的最新一条记录的情况。例如，我们有一个订单表，每个订单可能有多个商品，我们需要获取每个订单中最新添加的商品信息。本文将向您介绍如何使用MySQL来实现这一功能。

准备数据

首先，我们需要创建一个示例表来模拟订单表。我们创建一个名为orders的表，包含以下字段：

order_id：订单ID，主键
product_id：商品ID
product_name：商品名称
create_time：创建时间

使用以下SQL语句创建示例表：

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_id INT,
  product_name VARCHAR(100),
  create_time TIMESTAMP
);

接下来，我们往表中插入一些示例数据：

INSERT INTO orders (order_id, product_id, product_name, create_time)
VALUES
  (1, 1, '商品A', '2021-01-01 10:00:00'),
  (1, 2, '商品B', '2021-01-02 09:00:00'),
  (2, 3, '商品C', '2021-02-01 08:00:00'),
  (2, 4, '商品D', '2021-02-01 10:00:00'),
  (3, 5, '商品E', '2021-03-01 12:00:00');

现在我们已经准备好了示例数据，接下来开始实现获取每个订单中最新商品的功能。

方案一：使用子查询

一种常用的方法是使用子查询来获取每个分组的最新一条记录。具体步骤如下：

创建一个子查询，查询每个订单中最新的创建时间。
根据子查询的结果，再次查询对应的商品信息。

下面是MySQL代码示例：

SELECT o.order_id, o.product_id, o.product_name, o.create_time
FROM orders o
JOIN (
  SELECT order_id, MAX(create_time) AS latest_time
  FROM orders
  GROUP BY order_id
) t ON o.order_id = t.order_id AND o.create_time = t.latest_time;

以上查询语句中，我们首先创建一个子查询，将每个订单的最新创建时间查询出来。然后，将子查询的结果与原始表格进行关联，通过订单ID和最新创建时间进行匹配，找到对应的商品信息。

这种方法简单有效，可以满足大部分场景的需求。但是，如果数据量较大，性能可能会受到影响。

方案二：使用窗口函数

MySQL 8.0及以上版本提供了窗口函数（Window Function）功能，可以更方便地获取每个分组的最新一条记录。使用窗口函数，我们可以在查询结果中直接添加一个列，用于标记每个分组中的最新记录。

下面是使用窗口函数的MySQL代码示例：

SELECT order_id, product_id, product_name, create_time
FROM (
  SELECT order_id, product_id, product_name, create_time,
    ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY create_time DESC) AS rn
  FROM orders
) t
WHERE rn = 1;

以上查询语句中，我们首先将原始表格进行查询，并使用窗口函数ROW_NUMBER()来为每个分组中的记录进行标号。PARTITION BY order_id表示按照order_id进行分组，ORDER BY create_time DESC表示按照create_time倒序排序。

然后，我们将这个查询结果作为子查询，并筛选出标号为1的记录，即每个分组中的最新记录。

这种方法相对于使用子查询的方法，代码更简洁，而且性能通常更好。但是需要注意的是，窗口函数在MySQL 8.0之前的版本是不支持的。