我有一张文件表(此处为简化版):

id rev content
1 1 ...
2 1 ...
1 2 ...
1 3 ...

如何为每个id选择一行,并且仅选择最大的rev?对于上面的数据,结果应该包含两行:[1,3,…]和[2,1,…]。我使用的是MySQL。

目前,我在while循环中使用检查来检测和重写结果集中的旧版本。但这是实现结果的唯一方法吗?没有SQL解决方案吗?


当前回答

下面是另一个解决方案,用于仅检索具有该字段最大值的字段的记录。这适用于我工作的平台SQL400。在本例中,字段FIELD5中具有最大值的记录将由以下SQL语句检索。

SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5
  FROM MYFILE A
 WHERE RRN(A) IN
   (SELECT RRN(B) 
      FROM MYFILE B
     WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2
     ORDER BY B.FIELD5 DESC
     FETCH FIRST ROW ONLY)

其他回答

如果select语句中有多个字段,并且希望通过优化代码获得所有这些字段的最新值:

select * from
(select * from table_name
order by id,rev desc) temp
group by id 

当您将rev和id组合为MAX()的一个maxRevId值,然后将其拆分为原始值时,可以在不进行联接的情况下进行选择:

SELECT maxRevId & ((1 << 32) - 1) as id, maxRevId >> 32 AS rev
FROM (SELECT MAX(((rev << 32) | id)) AS maxRevId
      FROM YourTable
      GROUP BY id) x;

当存在复杂联接而不是单个表时,这一速度特别快。使用传统方法,复杂连接将进行两次。

当rev和id为INT UNSIGNED(32位)并且组合值适合BIGINT UNSIGNED时(64位),上述组合对于位函数很简单。当id和rev大于32位值或由多个列组成时,需要将该值合并为二进制值,例如,为MAX()添加适当的填充。

我惊讶地发现,没有答案提供SQL窗口函数解决方案:

SELECT a.id, a.rev, a.contents
  FROM (SELECT id, rev, contents,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) ranked_order
          FROM YourTable) a
 WHERE a.ranked_order = 1 

在SQL标准ANSI/ISO标准SQL:2003中添加了窗口(或窗口)功能,后来又用ANSI/ISO标准SQL:2008进行了扩展。有更多类型的秩函数可用于处理平局问题:rank、DENSE_rank、PERSENT_rank。

乍一看。。。

您只需要一个带有MAX聚合函数的GROUP BY子句:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

从来没有这么简单,是吗?

我刚刚注意到你也需要内容栏。

这是SQL中一个非常常见的问题:在每个组标识符的列中查找具有某个最大值的行的整个数据。在我的职业生涯中我听过很多。事实上,这是我在当前工作的技术面试中回答的问题之一。

事实上,StackOverflow社区创建了一个标签来处理这样的问题是非常常见的:每个组中最优秀的一个。

基本上,有两种方法可以解决这个问题:

使用简单组标识符连接,组子查询中的最大值

在这种方法中,首先在子查询中找到组标识符,即组中的最大值(上面已经解决)。然后,将表连接到子查询,组标识符和组中的最大值相等:

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

左侧使用自身连接,调整连接条件和过滤器

在这种方法中,您可以将表与自身连接起来。组标识符中包含相等项。然后,两个聪明的动作:

第二个连接条件的左侧值小于右侧值执行步骤1时,实际具有最大值的行的右侧将为NULL(这是一个LEFT JOIN,记得吗?)。然后,我们过滤合并结果,只显示右侧为NULL的行。

所以你最终会得到:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

结论

这两种方法带来的结果完全相同。

如果组标识符的组中有两行具有最大值,则两种方法中的结果都是这两行。

这两种方法都与SQLANSI兼容,因此,无论其“味道”如何,都可以与您喜爱的RDBMS一起使用。

这两种方法都是性能友好的,但是您的里程可能会有所不同(RDBMS、DB结构、索引等)。确保你选择对你最有意义的那一个。

由于这是关于这个问题最流行的问题,我将在这里重新发布另一个答案:

看起来有更简单的方法(但仅在MySQL中):

select *
from (select * from mytable order by id, rev desc ) x
group by id

请感谢用户Bohemian在这个问题上的回答,因为它为这个问题提供了如此简洁而优雅的答案。

编辑:虽然这个解决方案对很多人都有效,但从长远来看它可能并不稳定,因为MySQL不能保证GROUP BY语句会为不在GROUP BY列表中的列返回有意义的值。因此,使用此解决方案的风险自负!