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.postidFROM Posts p1INNER 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.postidFROM 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;
