如何在mysql中创建只显示最后一行的组?[重复]

子查询解决方案 fiddle Link

select * from messages where id in
(select max(id) from messages group by Name)

按连接条件求解 fiddle link

select m1.* from messages m1 
left outer join messages m2 
on ( m1.id<m2.id and m1.name=m2.name )
where m2.id is null

这篇文章的原因是只给小提琴链接。 其他答案中已经提供了相同的sql。

我得到了一个不同的解决方案,即获取每个组中最后一篇文章的id,然后使用第一个查询的结果作为 WHERE x IN 构建:

SELECT id, name, other_columns
FROM messages
WHERE id IN (
    SELECT MAX(id)
    FROM messages
    GROUP BY name
);

与其他一些解决方案相比,我不知道这是如何执行的,但对于我的表来说,它的工作非常出色,有300多万行。(4秒执行,1200+个结果)

这应该在mysql和sql server上都有效。

用你的 subquery 返回正确的分组,因为您已经完成了一半。

试试这个:

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

这样,就避免了子查询中的相关子查询和/或排序,这往往会非常慢/效率低下。

UPD:2017-03-31,版本 5.7.5 在mysql中,默认情况下只启用了按开关的完整组(因此,禁用了不确定的按查询分组)。此外,他们按实现更新了组,即使使用禁用的交换机,解决方案也可能不再按预期工作。需要检查一下。

当组内的项计数很小时,bill karwin的上述解决方案可以正常工作,但是当组很大时,查询的性能就会变差,因为该解决方案需要大约 n*n/2 + n/2 仅有的 IS NULL 比较。

我在innodb表上做了测试 18684446 行与 1182 组。该表包含功能测试的测试结果,并具有 (test_id, request_id) 作为主键。因此, test_id 是一个小组,我在寻找最后一个 request_id 对于每一个 试验样品 .

Bill的解决方案已经在我的Dell E4310上运行了几个小时,我不知道它什么时候完成,即使它在覆盖率索引上运行(因此 using index 在解释中)。

我还有两个基于相同想法的解决方案:

• 如果基础索引是btree index(通常是这种情况),则最大的 (group_id, item_value) pair是每个 group_id ,这是第一个 群标识 如果我们按降序遍历索引;
• 如果我们读取索引包含的值,则按索引的顺序读取这些值;
• 每个索引隐式地包含附加到该索引的主键列(即主键在覆盖率索引中)。在下面的解决方案中,我直接操作主键,在您的情况下,您只需要在结果中添加主键列。
• 在许多情况下,在子查询中按所需的顺序收集所需的行id并将子查询的结果连接到id上要便宜得多。由于子查询结果中的每一行mysql都需要一个基于主键的提取,因此子查询将首先放在连接和行中将按子查询中ID的顺序输出(如果我们为联接省略显式的ORDERBY)

3 ways MySQL uses indexes 是了解一些细节的好文章。

解决方案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个最大行。

查询的缺点是查询缓存无法缓存其结果。

MySQL8.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 data dump . 我会用它来做基准测试。共有1114357行 Posts 表。正在运行 MySQL 5.0.75在我的MacBook Pro 2.40GHz上。

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

第一次使用技术 shown 由@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 analysis 超过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)

现在使用 my technique 具有 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)

这个 解释 分析表明,这两个表都可以使用其索引:

+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
| 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)

这是我的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;