检索每个组中的最后一条记录
有一个表格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运行查询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 August数据转储的副本。 我会用它来进行基准测试。 在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;
UPD：2017-03-31，MySQL 5.7.5版本默认启用了ONLY_FULL_GROUP_BY开关（因此，非确定性GROUP BY查询被禁用）。 此外，他们更新了GROUP BY实施，即使使用禁用的开关，该解决方案也可能无法按预期工作。 一个需要检查。
上面的Bill Karwin的解决方案在组内的项数很小时工作正常，但当组比较大时，查询性能变差，因为解决方案仅需要大约n*n/2 + n/2个IS NULL比较。
我在1182组的18684446行的InnoDB表上做了测试。 该表包含功能测试的测试结果，并具有(test_id, request_id)作为主键。 因此， test_id是一个组，我正在为每个test_id搜索最后一个request_id 。
比尔的解决方案已经在我的戴尔e4310上运行了几个小时，我不知道它何时完成，即使它在覆盖索引上运行（因此在EXPLAIN中using index ）。
我有一些基于相同想法的其他解决方案：
(group_id, item_value)对是每个内的最后值group_id ，即第一个为每一个group_id如果我们以降序穿行的索引; MySQL使用索引的3种方式是理解一些细节的好文章。
解决方案1
这个速度非常快，我的18M +行大概需要0.8秒。
SELECT test_id, MAX(request_id), request_id
FROM testresults
GROUP BY test_id DESC;
如果要将订单更改为ASC，请将其置于子查询中，仅返回id并将其用作子查询以加入其余列：
SELECT test_id, request_id
FROM (
SELECT test_id, MAX(request_id), request_id
FROM testresults
GROUP BY test_id DESC) as ids
ORDER BY test_id;
我的数据大概需要1,2秒。
解决方案2
这是另一个解决方案，我的表需要大约19秒钟的时间：
SELECT test_id, request_id
FROM testresults, (SELECT @group:=NULL) as init
WHERE IF(IFNULL(@group, -1)=@group:=test_id, 0, 1)
ORDER BY test_id DESC, request_id DESC
它也以降序的方式返回测试。 由于它进行了完整的索引扫描，速度要慢得多，但它可以让您知道如何为每个组输出N max行。
查询的缺点是它的结果不能被查询缓存缓存。
使用你的子查询返回正确的分组，因为你在那里。
尝试这个：
select
a.*
from
messages a
inner join
(select name, max(id) as maxid from messages group by name) as b on
a.id = b.maxid
如果它不是id你需要最大的：
select
a.*
from
messages a
inner join
(select name, max(other_col) as other_col
from messages group by name) as b on
a.name = b.name
and a.other_col = b.other_col
这样，您就避免了子查询中的相关子查询和/或排序，这往往是非常缓慢/低效的。
链接地址: http://www.djcxy.com/p/73393.html上一篇: Retrieving the last record in each group
下一篇: only minus's