如何在MySQL中的每个组中有效找到最后一个记录？

MySQL高效获取每个分组的最后一条记录

在SQL中，提取每个分组的最新记录可能比较棘手。常见场景是：表中有多条记录共享同一个主键，需要提取每个主键的最新条目。本文提供了一种高效且优化的解决方案。

问题描述

考虑以下messages表：

Id	Name	Other_Columns
1	A	A_data_1
2	A	A_data_2
3	A	A_data_3
4	B	B_data_1
5	B	B_data_2
6	C	C_data_1

使用SELECT * FROM messages GROUP BY Name的基本分组查询会产生以下结果：

Id	Name	Other_Columns
1	A	A_data_1
4	B	B_data_1
6	C	C_data_1

然而，期望的输出是每个分组的最新记录：

Id	Name	Other_Columns
3	A	A_data_3
5	B	B_data_2
6	C	C_data_1

解决方案

使用窗口函数 (MySQL 8.0 及更高版本)

MySQL 8.0 引入了窗口函数，为查找每个分组的最后一条记录提供了一种优雅的解决方案。以下查询利用ROW_NUMBER()函数实现此目的：

WITH ranked_messages AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
  FROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;

使用LEFT JOIN (MySQL早期版本)

在 MySQL 8.0 之前，最有效的解决方案是使用 LEFT JOIN：

SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;

性能和适用性

虽然两种解决方案都很高效，但它们的性能可能会因数据的大小和分布而异。对于大型且多样化的数据集，通常建议使用窗口函数方法。

如果数据呈现特定模式（例如每个分组的记录相对较少），则 LEFT JOIN 方法可能更高效。

基准测试

在许多情况下，LEFT JOIN 方法已被证明优于分组技术。例如，在一个拥有数百万行的大型数据库中，LEFT JOIN 方法的执行时间不到一秒，而分组技术则需要一分钟以上。

但是，始终建议在您的特定数据集上测试这两种解决方案，以确定最佳方法。

以上是如何在MySQL中的每个组中有效找到最后一个记录？的详细内容。更多信息请关注PHP中文网其他相关文章！