我有一张文件表(此处为简化版):
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解决方案吗?
当前回答
我惊讶地发现,没有答案提供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。
其他回答
当您将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()添加适当的填充。
另一种解决方案是使用相关子查询:
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个连接时,它的性能会受到组大小的严重影响。。。
另一种方法是在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表示表中的记录数!
像这样吗?
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)
我想,你想要这个吗?
select * from docs where (id, rev) IN (select id, max(rev) as rev from docs group by id order by id)
SQL Fiddle:在此处检查