MySQL取分组后最新的一条数据

ZRDX • 2024-10-26 11:55 • 编程

一、背景介绍

MySQL是当前使用最广泛的关系型数据库管理系统，功能强大，应用广泛。在业务开发过程中，经常需要对数据进行分组，然后从分组中取出最新的一条数据。

例如，某电商平台需要统计每个商品的销售情况，需要按照商品分组，然后取出每个商品最新的销售记录。这个需求在开发过程中非常常见。

二、基本方法

对于这个需求，我们可以使用MySQL中的子查询和GROUP BY语句来实现。

举例来说，假设我们有一个sales表，其中存储了每个商品的销售记录：

CREATE TABLE sales (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    order_time DATETIME NOT NULL,
    order_quantity INT NOT NULL
);

INSERT INTO sales (product_id, order_time, order_quantity) VALUES (1, '2022-01-01', 10);
INSERT INTO sales (product_id, order_time, order_quantity) VALUES (1, '2022-01-02', 11);
INSERT INTO sales (product_id, order_time, order_quantity) VALUES (2, '2022-01-01', 5);
INSERT INTO sales (product_id, order_time, order_quantity) VALUES (2, '2022-01-03', 12);
INSERT INTO sales (product_id, order_time, order_quantity) VALUES (3, '2022-01-02', 8);
INSERT INTO sales (product_id, order_time, order_quantity) VALUES (3, '2022-01-03', 6);

我们可以使用以下SQL语句，按照product_id分组，然后取出每组中order_time最大的那条记录：

SELECT s1.*
FROM sales s1
JOIN (
    SELECT product_id, MAX(order_time) max_time
    FROM sales
    GROUP BY product_id
) s2 ON s1.product_id = s2.product_id AND s1.order_time = s2.max_time;

该SQL语句分为两部分，第一部分是一个子查询，用来计算每个product_id的最大订单时间。第二部分是一个JOIN语句，用来将sales表与第一部分查询结果连接，以获取最新的销售记录。

三、相关技巧

1. 使用LIMIT语句优化性能

虽然上述SQL语句可以正确地获取每个product_id的最新销售记录，但是在sales表比较大时会性能比较差。因为该查询会先计算出每个product_id的最大订单时间，然后再和全部的sales表进行JOIN。

为了优化性能，我们可以使用LIMIT语句来限制JOIN所使用的行数。具体来说，我们可以将子查询查询结果中的每个product_id的最大订单时间，排序后取前N个结果，然后只对这些结果执行JOIN。这样可以大大减少JOIN所需要的行数，从而提高性能。

SELECT s1.*
FROM sales s1
JOIN (
    SELECT product_id, MAX(order_time) max_time
    FROM sales
    GROUP BY product_id
    ORDER BY max_time DESC
    LIMIT 10 -- 只取前10个结果
) s2 ON s1.product_id = s2.product_id AND s1.order_time = s2.max_time;

该SQL语句在子查询中限制了结果集的大小，只返回了每个product_id的最近10个销售记录。在实际使用时，可以根据具体情况调整LIMIT的值。

2. 使用窗口函数

在MySQL 8.0版本中，新增了窗口函数相关的语法，可以简化上述查询。具体来说，我们可以使用ROW_NUMBER()函数生成每个product_id内的销售记录的排名，然后只取销售记录排名为1的记录。

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY order_time DESC) AS rank
    FROM sales
) s
WHERE s.rank = 1;

该SQL语句使用了窗口函数ROW_NUMBER()，并将分组方式和排序方式指定在OVER后面的子句中。该查询会生成一个rank列，表示每个product_id内的销售记录的排名。最后一步只需要筛选rank为1的记录，即每个product_id的最新销售记录。

四、总结

在业务开发中，经常需要对数据进行分组，然后取出每组中最新的一条数据。MySQL中提供了多种实现方式，其中常用的是子查询和GROUP BY语句。在使用时，可以根据具体场景考虑使用LIMIT语句或者窗口函数等优化方式，以提高查询效率。

原创文章，作者：ZRDX，如若转载，请注明出处：https://www.506064.com/n/145082.html