我有一张文件表(此处为简化版):
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解决方案吗?
当前回答
这些答案对我来说都不管用。
这就是对我有用的。
with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max
其他回答
下面是另一个解决方案,用于仅检索具有该字段最大值的字段的记录。这适用于我工作的平台SQL400。在本例中,字段FIELD5中具有最大值的记录将由以下SQL语句检索。
SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5
FROM MYFILE A
WHERE RRN(A) IN
(SELECT RRN(B)
FROM MYFILE B
WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2
ORDER BY B.FIELD5 DESC
FETCH FIRST ROW ONLY)
我想,你想要这个吗?
select * from docs where (id, rev) IN (select id, max(rev) as rev from docs group by id order by id)
SQL Fiddle:在此处检查
解释
这不是纯SQL。这将使用SQLAlchemy ORM。
我来这里寻求SQLAlchemy帮助,所以我将使用python/SQLAlchemi版本复制Adrian Carneiro的答案,特别是外部连接部分。
此查询回答了以下问题:
“你能把这组记录(基于相同id)中版本号最高的记录还给我吗”。
这允许我复制记录,更新它,增加它的版本号,并以一种可以显示随时间变化的方式复制旧版本。
Code
MyTableAlias = aliased(MyTable)
newest_records = appdb.session.query(MyTable).select_from(join(
MyTable,
MyTableAlias,
onclause=and_(
MyTable.id == MyTableAlias.id,
MyTable.version_int < MyTableAlias.version_int
),
isouter=True
)
).filter(
MyTableAlias.id == None,
).all()
在PostgreSQL数据库上测试。
乍一看。。。
您只需要一个带有MAX聚合函数的GROUP BY子句:
SELECT id, MAX(rev)
FROM YourTable
GROUP BY id
从来没有这么简单,是吗?
我刚刚注意到你也需要内容栏。
这是SQL中一个非常常见的问题:在每个组标识符的列中查找具有某个最大值的行的整个数据。在我的职业生涯中我听过很多。事实上,这是我在当前工作的技术面试中回答的问题之一。
事实上,StackOverflow社区创建了一个标签来处理这样的问题是非常常见的:每个组中最优秀的一个。
基本上,有两种方法可以解决这个问题:
使用简单组标识符连接,组子查询中的最大值
在这种方法中,首先在子查询中找到组标识符,即组中的最大值(上面已经解决)。然后,将表连接到子查询,组标识符和组中的最大值相等:
SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
SELECT id, MAX(rev) rev
FROM YourTable
GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev
左侧使用自身连接,调整连接条件和过滤器
在这种方法中,您可以将表与自身连接起来。组标识符中包含相等项。然后,两个聪明的动作:
第二个连接条件的左侧值小于右侧值执行步骤1时,实际具有最大值的行的右侧将为NULL(这是一个LEFT JOIN,记得吗?)。然后,我们过滤合并结果,只显示右侧为NULL的行。
所以你最终会得到:
SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;
结论
这两种方法带来的结果完全相同。
如果组标识符的组中有两行具有最大值,则两种方法中的结果都是这两行。
这两种方法都与SQLANSI兼容,因此,无论其“味道”如何,都可以与您喜爱的RDBMS一起使用。
这两种方法都是性能友好的,但是您的里程可能会有所不同(RDBMS、DB结构、索引等)。确保你选择对你最有意义的那一个。
另一种解决方案是使用相关子查询:
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个连接时,它的性能会受到组大小的严重影响。。。