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

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

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

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


当前回答

这是一个很好的方法

使用以下代码:

with temp as  ( 
select count(field1) as summ , field1
from table_name
group by field1 )
select * from temp where summ = (select max(summ) from temp)

其他回答

我喜欢通过按某一列对记录进行排名来做到这一点。在这种情况下,按id对rev值进行分组。rev值越高的排名越低。所以,最高转速的排名为1。

select id, rev, content
from
 (select
    @rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num,
    id, rev, content,
    @prevValue := id
  from
   (select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP,
   (select @rowNum := 1 from DUAL) X,
   (select @prevValue := -1 from DUAL) Y) TEMP
where row_num = 1;

不确定引入变量是否会使整个过程变慢。但至少我没有两次查询YOURTABLE。

我想,你想要这个吗?

select * from docs where (id, rev) IN (select id, max(rev) as rev from docs group by id order by id)  

SQL Fiddle:在此处检查

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

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

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

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

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

注意:在MySQL 8+天中,我可能不会再推荐这种方法了。好几年没用了。

第三种解决方案是MySQL特有的,看起来像这样:

SELECT id, MAX(rev) AS rev
 , 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id

是的,它看起来很糟糕(转换为字符串和返回等),但根据我的经验,它通常比其他解决方案更快。也许这只是我的用例,但我已经在具有数百万条记录和许多唯一ID的表上使用了它。也许是因为MySQL在优化其他解决方案方面非常糟糕(至少在我提出这个解决方案的5.0天)。

一件重要的事情是GROUP_CONCAT对于它可以建立的字符串有一个最大长度。您可能希望通过设置group_concat_max_len变量来提高此限制。请记住,如果您有大量的行,这将是缩放的限制。

无论如何,如果您的内容字段已经是文本,则上述操作不会直接起作用。在这种情况下,您可能需要使用不同的分隔符,例如\0。您还将更快地达到group_concat_max_len限制。

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

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