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

Question

有一个表 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 ，我会得到如下结果：

 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

但这看起来效率很低。还有其他方法可以达到相同的结果吗？

原文由 Vijay Dev 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

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;

MySQL 手册中说明了这种和其他查找 分组最大行 的方法。

以下是我在 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 data dump 的副本。我将使用它进行基准测试。 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 在这里向您展示如何进行软件开发工作，而不是为您完成所有工作。

原文由 Bill Karwin 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

我有一个类似的问题

子查询并加入救援

SELECT p."Date"
        ,p."Symbol"
        ,p."ratio_roll_qtr_ret"
    FROM PUBLIC."prices_vw" AS p
    JOIN (
        SELECT "Symbol"
            ,max("Date")
        FROM PUBLIC."prices_vw"
        GROUP BY "Symbol"
        ) AS sq ON p."Date" = sq."max"
        AND p."Symbol" = sq."Symbol"
    WHERE p."ratio_roll_qtr_ret" IS NOT NULL
    ORDER BY "ratio_roll_qtr_ret" DESC;

原文由 thistleknot 发布，翻译遵循 CC BY-SA 4.0 许可协议