mysql查询每组数据的最后一条_检索每个组中的最后一条记录 - MySQL

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数据转储的副本。我会用它来进行基准测试。表中有1,114,357行Posts。这是在我的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)

+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+

| id | select_type | table      | type   | possible_keys              | key         | key_len | ref          | rows    | Extra       |

+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+

|  1 | PRIMARY     | | 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;