我有一张文件表(此处为简化版):
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 yourtable.id, rev, content
FROM yourtable
INNER JOIN (
SELECT id, max(rev) as maxrev
FROM yourtable
GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)
其他回答
我喜欢使用基于NOT EXIST的解决方案来解决这个问题:
SELECT
id,
rev
-- you can select other columns here
FROM YourTable t
WHERE NOT EXISTS (
SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)
这将选择组中具有最大值的所有记录,并允许您选择其他列。
由于这是关于这个问题最流行的问题,我将在这里重新发布另一个答案:
看起来有更简单的方法(但仅在MySQL中):
select *
from (select * from mytable order by id, rev desc ) x
group by id
请感谢用户Bohemian在这个问题上的回答,因为它为这个问题提供了如此简洁而优雅的答案。
编辑:虽然这个解决方案对很多人都有效,但从长远来看它可能并不稳定,因为MySQL不能保证GROUP BY语句会为不在GROUP BY列表中的列返回有意义的值。因此,使用此解决方案的风险自负!
另一种解决方案是使用相关子查询:
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个连接时,它的性能会受到组大小的严重影响。。。
这是一个很好的方法
使用以下代码:
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)
我想,你想要这个吗?
select * from docs where (id, rev) IN (select id, max(rev) as rev from docs group by id order by id)
SQL Fiddle:在此处检查