我有一张文件表(此处为简化版):
id | rev | content |
---|---|---|
1 | 1 | ... |
2 | 1 | ... |
1 | 2 | ... |
1 | 3 | ... |
如何为每个id选择一行,并且仅选择最大的rev?对于上面的数据,结果应该包含两行:[1,3,…]和[2,1,…]。我使用的是MySQL。
目前,我在while循环中使用检查来检测和重写结果集中的旧版本。但这是实现结果的唯一方法吗?没有SQL解决方案吗?
我有一张文件表(此处为简化版):
id | rev | content |
---|---|---|
1 | 1 | ... |
2 | 1 | ... |
1 | 2 | ... |
1 | 3 | ... |
如何为每个id选择一行,并且仅选择最大的rev?对于上面的数据,结果应该包含两行:[1,3,…]和[2,1,…]。我使用的是MySQL。
目前,我在while循环中使用检查来检测和重写结果集中的旧版本。但这是实现结果的唯一方法吗?没有SQL解决方案吗?
当前回答
这是另一个解决方案,希望它能帮助某人
Select a.id , a.rev, a.content from Table1 a
inner join
(SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev
其他回答
注意:在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
由于这是关于这个问题最流行的问题,我将在这里重新发布另一个答案:
看起来有更简单的方法(但仅在MySQL中):
select *
from (select * from mytable order by id, rev desc ) x
group by id
请感谢用户Bohemian在这个问题上的回答,因为它为这个问题提供了如此简洁而优雅的答案。
编辑:虽然这个解决方案对很多人都有效,但从长远来看它可能并不稳定,因为MySQL不能保证GROUP BY语句会为不在GROUP BY列表中的列返回有意义的值。因此,使用此解决方案的风险自负!
另一种方法是在OVERPARTITION子句中使用MAX()分析函数
SELECT t.*
FROM
(
SELECT id
,rev
,contents
,MAX(rev) OVER (PARTITION BY id) as max_rev
FROM YourTable
) t
WHERE t.rev = t.max_rev
本文中已经记录的另一个ROW_NUMBER()OVERPARTITION解决方案是
SELECT t.*
FROM
(
SELECT id
,rev
,contents
,ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
FROM YourTable
) t
WHERE t.rank = 1
此2 SELECT在Oracle 10g上运行良好。
MAX()解决方案的运行速度肯定比ROW_NUMBER()方案快,因为MAX()复杂性为O(n),而ROW_NUMBER()复杂性最低为O(n.log(n)),其中n表示表中的记录数!
这是一个很好的方法
使用以下代码:
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)