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

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

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

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


当前回答

这个怎么样:

SELECT all_fields.*  
FROM (SELECT id, MAX(rev) FROM yourtable GROUP BY id) AS max_recs  
LEFT OUTER JOIN yourtable AS all_fields 
ON max_recs.id = all_fields.id

其他回答

我用下面的方法解决了我自己的问题。我首先创建了一个临时表,并插入了每个唯一id的最大rev值。

CREATE TABLE #temp1
(
    id varchar(20)
    , rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM 
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as a 
GROUP BY a.id
ORDER BY a.id

然后,我将这些最大值(#temp1)加入到所有可能的id/内容组合中。通过这样做,我很自然地过滤掉了非最大id/内容组合,并且只剩下每个组合的最大rev值。

SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id

另一种解决方案是使用相关子查询:

select yt.id, yt.rev, yt.contents
    from YourTable yt
    where rev = 
        (select max(rev) from YourTable st where yt.id=st.id)

在(id,rev)上设置索引几乎可以将子查询呈现为一个简单的查找。。。

以下是与@AdrianCarneiro的答案(subquery,leftjoin)中的解决方案的比较,基于MySQL的测量结果,InnoDB表的记录约为100万条,组大小为:1-3。

虽然对于全表扫描,子查询/左连接/相关定时彼此之间的关系为6/8/9,但当涉及到直接查找或批处理((1,2,3)中的id)时,子查询比其他查询慢得多(由于重新运行子查询)。然而,我无法在速度上区分左连接和相关解决方案。

最后一点,当leftjoin在组中创建n*(n+1)/2个连接时,它的性能会受到组大小的严重影响。。。

我想,你想要这个吗?

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

SQL Fiddle:在此处检查

注意:在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 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