有一个表 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
但这看起来非常低效 . 还有其他方法可以达到同样的效果吗?
21 回答
这是我的解决方案:
试试这个:
嗨@Vijay Dev如果你的表 messages 包含 Id 这是自动增量主键然后在主键上获取最新记录基础,你的查询应如下所示:
我们有什么方法可以使用这种方法删除表中的重复项吗?结果集基本上是一组唯一记录,所以如果我们可以删除不在结果集中的所有记录,我们实际上没有重复记录?我试过这个,但mySQL给出了1093错误 .
有没有办法可以将输出保存到临时变量然后从NOT IN(临时变量)中删除? @Bill感谢您提供非常有用的解决方案 .
编辑:想想我找到了解决方案:
根据您的问题,以下查询将正常工作 .
这个怎么样:
我有类似的问题(在postgresql艰难)和1M记录表上 . 这个解决方案需要1.7s而不是由LEFT JOIN生成的44s . 在我的情况下,我必须过滤你的名字字段的对应的NULL值,导致更好的性能0.2秒
如果您想要每个
Name
的最后一行,那么您可以通过Name
为每个行组分配行号,并按Id
降序排序 .QUERY
SQL小提琴
这是另一种获取最后一个相关记录的方法,使用
GROUP_CONCAT
和order bySUBSTRING_INDEX
从列表中选择一条记录上面的查询将对同一
Name
组中的所有Other_Columns
进行分组,并且使用ORDER BY id DESC
将以特定组中的所有Other_Columns
加入所提供的分隔符,在我的情况下我使用||
,使用SUBSTRING_INDEX
在此列表上将选择第一个一小提琴演示
如果性能确实是您关心的问题,您可以在表格中引入一个名为
IsLastInGroup
的BIT类型的新列 .在最后一列上将其设置为true,并在每行插入/更新/删除时对其进行维护 . 写入会比较慢,但是你会在阅读上受益 . 这取决于您的使用案例,我建议只有在您专注于阅读时 .
所以你的查询看起来像:
UPD:2017-03-31,MySQL版本5.7.5默认启用ONLY_FULL_GROUP_BY开关(因此,非确定性GROUP BY查询被禁用) . 此外,他们更新了GROUP BY实施,即使使用禁用的交换机,解决方案也可能无法正常工作 . 一个人需要检查 .
当组内的项目数量相当小时,Bill Karwin的上述解决方案可以正常工作,但是当组相当大时,查询的性能会变差,因为解决方案只需要
IS NULL
比较 .我在带有
1182
组的18684446
行的InnoDB表上进行了测试 . 该表包含功能测试的测试结果,并将(test_id, request_id)
作为主键 . 因此,test_id
是一个组,我正在为每个test_id
搜索最后一个request_id
.Bill的解决方案已经在我的dell e4310上运行了几个小时,我不知道它什么时候会完成,即使它在覆盖索引上运行(因此在EXPLAIN中为
using index
) .我有几个基于相同想法的其他解决方案:
如果基础索引是BTREE索引(通常是这种情况),则最大
(group_id, item_value)
对是每个group_id
中的最后一个值,如果我们按降序遍历索引,则每个group_id
的第一个值 .如果我们读取索引所涵盖的值,则按索引的顺序读取值;
每个索引隐式包含附加到其上的主键列(即主键位于coverage索引中) . 在下面的解决方案中,我直接在主键上操作,在这种情况下,您只需要在结果中添加主键列 .
在许多情况下,在子查询中以所需顺序收集所需的行ID并在id上加入子查询的结果要便宜得多 . 因为对于子查询结果中的每一行,MySQL将需要基于主键的单个提取,子查询将首先放在连接中,并且将按子查询中的id的顺序输出行(如果我们省略显式的ORDER BY为了加入)
3 ways MySQL uses indexes是一篇了解一些细节的好文章 .
Solution 1
这个速度非常快,我的18M行需要大约0.8秒:
如果要将顺序更改为ASC,请将其放在子查询中,仅返回id并将其用作子查询加入其余列:
这个数据大约需要1,2秒 .
Solution 2
这是我的 table 需要大约19秒的另一个解决方案:
它也以降序返回测试 . 由于它执行完整的索引扫描,因此速度要慢得多,但它可以让您了解如何为每个组输出N个最大行数 .
查询的缺点是查询缓存无法缓存其结果 .
使用subquery返回正确的分组,因为你已经到了一半 .
试试这个:
如果它不是
id
你想要最大值:这样,您可以避免子查询中的相关子查询和/或排序,这些子查询往往非常慢/效率低 .
我到达了另一个解决方案,即获取每个组中最后一篇文章的ID,然后使用第一个查询的结果作为
WHERE x IN
构造的参数从messages表中进行选择:与其他一些解决方案相比,我不知道它的表现如何,但它对于我的 table 有300万行非常出色 . (4次执行,1200次结果)
这应该适用于MySQL和SQL Server .
我还没有使用大型数据库进行测试,但我认为这可能比连接表更快:
具有相当速度的方法如下 .
Result
你也可以从这里看看 .
http://sqlfiddle.com/#!9/ef42b/9
FIRST SOLUTION
SECOND SOLUTION
MySQL 8.0现在支持窗口函数,就像几乎所有流行的SQL实现一样 . 使用此标准语法,我们可以编写最大n组的查询:
以下是我在2009年为这个问题写的原始答案:
我这样写解决方案:
关于性能,一种解决方案或另一种解决方案可能更好,具体取决于数据的性质 . 因此,您应该测试两个查询并使用在给定数据库时性能更好的查询 .
例如,我有StackOverflow August data dump的副本 . 我会用它来进行基准测试 .
Posts
表中有1,114,357行 . 这是在我的Macbook Pro 2.40GHz上运行MySQL 5.0.75 .我将编写一个查询来查找给定用户ID(我的)的最新帖子 .
First using the technique shown by @Eric with the GROUP BY in a subquery:
即使EXPLAIN analysis需要16秒:
Now produce the same query result using my technique with LEFT JOIN:
EXPLAIN
分析显示两个表都能够使用它们的索引:这是我的
Posts
表的DDL:这是两个建议 . 首先,如果mysql支持ROW_NUMBER(),那很简单:
我假设“最后”你的意思是最后一个顺序 . 如果不是,请相应地更改ROW_NUMBER()窗口的ORDER BY子句 . 如果ROW_NUMBER()不可用,这是另一种解决方案:
其次,如果没有,这通常是一个很好的方法:
换句话说,选择没有具有相同名称的later-Id消息的消息 .
很明显,有很多不同的方法可以获得相同的结果,你的问题似乎是在MySQL中获得最后结果的有效方法 . 如果您正在处理大量数据并假设您正在使用InnoDB甚至最新版本的MySQL(例如5.7.21和8.0.4-rc),那么可能没有一种有效的方法 .
我们有时需要使用行数超过6000万的表来执行此操作 .
对于这些示例,我将使用只有大约150万行的数据,其中查询需要查找数据中所有组的结果 . 在我们的实际情况中,我们经常需要从大约2,000个组返回数据(假设不需要检查大部分数据) .
我将使用下表:
温度表中包含大约150万个随机记录,以及100个不同的组 . selected_group填充了这100个组(在我们的例子中,对于所有组,这通常小于20%) .
由于此数据是随机的,因此意味着多行可以具有相同的recordedTimestamp . 我们想要的是按groupID的顺序获取所有选定组的列表,每个组包含最后一个recordedTimestamp,如果同一组具有多个匹配的行,那么这些行的最后一个匹配id .
如果假设MySQL有一个last()函数,它返回特殊ORDER BY子句中最后一行的值,那么我们可以简单地做:
在这种情况下,它只需要检查几百行,因为它不使用任何正常的GROUP BY函数 . 这将在0秒内执行,因此效率很高 . 请注意,通常在MySQL中我们会看到GROUP BY子句后面的ORDER BY子句,但是这个ORDER BY子句用于确定last()函数的ORDER,如果它在GROUP BY之后,那么它将对GROUPS进行排序 . 如果不存在GROUP BY子句,则所有返回的行中的最后一个值将相同 .
然而,MySQL没有这个,所以让我们看看它有什么不同的想法,并证明这些都不是有效的 .
Example 1
这检查了3,009,254行,在5.7.21上花费了大约0.859秒,在8.0.4-rc上花了更长的时间
Example 2
这检查了1,505,331行,在5.7.21上耗时约1.25秒,在8.0.4-rc略长
Example 3
这检查了3,009,685行,在5.7.21上花了大约1.95秒,在8.0.4-rc上花了稍长的时间
Example 4
这检查了6,137,810行,在5.7.21上花费了大约2.2秒,在8.0.4-rc上花了稍长的时间
Example 5
这检查了6,017,808行,在8.0.4-rc上花费了大约4.2秒
Example 6
这检查了6,017,908行,在8.0.4-rc上花费了大约17.5秒
Example 7
这个是永远的,所以我不得不杀了它 .
子查询解决方案fiddle Link
解决方案按连接条件fiddle link
这篇文章的原因是只提供小提琴链接 . 其他答案中已提供相同的SQL .