检索每个组中的最后一条记录 – MySQL

本教程将介绍检索每个组中的最后一条记录 - MySQL的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

检索每个组中的最后一条记录 - MySQL 教程 第1张

问题描述

有一个表messages包含如下数据:

IdNameOther_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,我会得到如下结果:

1 A A_data_1
4 B B_data_1
6 C C_data_1

什么查询会返回以下结果?

3 A A_data_3
5 B B_data_2
6 C C_data_1

即应该返回每组中的最后一条记录.

目前,这是我使用的查询:

SELECT
  *
FROM (SELECT
  *
FROM messages
ORDER BY id DESC) AS x
GROUP BY name

但这看起来非常低效.还有其他方法可以达到同样的效果吗?

推荐答案

MySQL 8.0 现在支持窗口函数,就像几乎所有流行的 SQL 实现一样.使用这种标准语法,我们可以编写每个组的最大 n 个查询:

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;

以下是我在 2009 年为这个问题写的原始答案:

我这样写解决方案:

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

就性能而言,一种或另一种解决方案可能会更好,具体取决于数据的性质.因此,您应该测试这两个查询并使用给定您的数据库性能更好的查询.

例如,我有一份StackOverflow 8 月数据转储.我将使用它进行基准测试.Posts 表中有 1,114,357 行.这是在我的 Macbook Pro 2.40GHz 上的 MySQL 5.0.75 上运行的.

我将编写一个查询来查找给定用户 ID(我的)的最新帖子.

首先使用技术显示 @Eric 在子查询中使用 GROUP BY:

SELECT p1.postid
FROM Posts p1
INNER JOIN (SELECT pi.owneruserid, MAX(pi.postid) AS maxpostid
FROM Posts pi GROUP BY pi.owneruserid) p2
  ON (p1.postid = p2.maxpostid)
WHERE p1.owneruserid = 20860;

1 row in set (1 min 17.89 sec)

即使是 EXPLAIN 分析 耗时 16 秒:

+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
| id | select_type | table| type| possible_keys  | key| key_len | ref | rows | Extra |
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
|  1 | PRIMARY  | <derived2> | ALL | NULL  | NULL  | NULL | NULL|76756 | | 
|  1 | PRIMARY  | p1| eq_ref | PRIMARY,PostId,OwnerUserId | PRIMARY  | 8 | p2.maxpostid | 1 | Using where | 
|  2 | DERIVED  | pi| index  | NULL  | OwnerUserId | 8 | NULL| 1151268 | Using index | 
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
3 rows in set (16.09 sec)

现在使用 我的技术 LEFT JOIN:

SELECT p1.postid
FROM Posts p1 LEFT JOIN posts p2
  ON (p1.owneruserid = p2.owneruserid AND p1.postid < p2.postid)
WHERE p2.postid IS NULL AND p1.owneruserid = 20860;

1 row in set (0.28 sec)

EXPLAIN 分析表明两个表都可以使用它们的索引:

+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
| id | select_type | table | type | possible_keys  | key| key_len | ref| rows | Extra  |
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
|  1 | SIMPLE| p1 | ref  | OwnerUserId | OwnerUserId | 8 | const | 1384 | Using index  | 
|  1 | SIMPLE| p2 | ref  | PRIMARY,PostId,OwnerUserId | OwnerUserId | 8 | const | 1384 | Using where; Using index; Not exists | 
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
2 rows in set (0.00 sec)

这是我的 Posts 表的 DDL:

CREATE TABLE `posts` (
  `PostId` bigint(20) unsigned NOT NULL auto_increment,
  `PostTypeId` bigint(20) unsigned NOT NULL,
  `AcceptedAnswerId` bigint(20) unsigned default NULL,
  `ParentId` bigint(20) unsigned default NULL,
  `CreationDate` datetime NOT NULL,
  `Score` int(11) NOT NULL default '0',
  `ViewCount` int(11) NOT NULL default '0',
  `Body` text NOT NULL,
  `OwnerUserId` bigint(20) unsigned NOT NULL,
  `OwnerDisplayName` varchar(40) default NULL,
  `LastEditorUserId` bigint(20) unsigned default NULL,
  `LastEditDate` datetime default NULL,
  `LastActivityDate` datetime default NULL,
  `Title` varchar(250) NOT NULL default '',
  `Tags` varchar(150) NOT NULL default '',
  `AnswerCount` int(11) NOT NULL default '0',
  `CommentCount` int(11) NOT NULL default '0',
  `FavoriteCount` int(11) NOT NULL default '0',
  `ClosedDate` datetime default NULL,
  PRIMARY KEY  (`PostId`),
  UNIQUE KEY `PostId` (`PostId`),
  KEY `PostTypeId` (`PostTypeId`),
  KEY `AcceptedAnswerId` (`AcceptedAnswerId`),
  KEY `OwnerUserId` (`OwnerUserId`),
  KEY `LastEditorUserId` (`LastEditorUserId`),
  KEY `ParentId` (`ParentId`),
  CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`PostTypeId`) REFERENCES `posttypes` (`PostTypeId`)
) ENGINE=InnoDB;

评论者注意:如果您想要使用不同版本的 MySQL、不同的数据集或不同的表设计进行另一个基准测试,请随意自己做.我已经展示了上面的技术.Stack Overflow 在这里向您展示怎么进行软件开发工作,而不是为您完成所有工作.

好了关于检索每个组中的最后一条记录 - MySQL的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。