针对“在组中选择最大值”的最佳效果查询？ [英] Best-performance query for "select max in group"?

问题描述

我有一个简单的表注释（id INT，revision INT，comment VARCHAR（140））有一些内容像这样：

I have a simple table comments (id INT, revision INT, comment VARCHAR(140)) with some content like this:

1|1|hallo1|
1|2|hallo2|
1|3|hallo3|
2|1|hallo1|
2|2|hallo2|

我正在搜索一个SQL语句，它将返回每个具有最高版本的注释：

I'm searching for an SQL statement which will return each comment with the highest revision:

1|3|hallo3|
2|2|hallo2|

我想出了这个解决方案：

I've come up with this solution:

select id, revision, comment 
  from comments 
  where revision = (
      select max(revision) 
        from comments as f 
        where f.id = comments.id
  );

但是对于大型数据集，它很慢。

but it is very slow on large data sets. Are there any better queries to accomplish this?

推荐答案

这里有一种方法，使用适当的索引不会非常慢， t使用子选择：

Here's one way that with appropriate indexing will not be heinously slow and it doesn't use a subselect:

SELECT comments.ID, comments.revision, comments.comment FROM comments 
LEFT OUTER JOIN comments AS maxcomments 
ON maxcomments.ID= comments.ID
AND maxcomments.revision > comments.revision
WHERE maxcomments.revision IS NULL

根据这里的查询改编：
http://www.xaprb.com/blog/2007/03/14/how-to-find-the-max-row-per-group-in-sql-without-subqueries/

Adapted from queries here: http://www.xaprb.com/blog/2007/03/14/how-to-find-the-max-row-per-group-in-sql-without-subqueries/

（来自google搜索：max group by sql）

(From google search: max group by sql)

这篇关于针对“在组中选择最大值”的最佳效果查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！