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

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

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

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


当前回答

如果select语句中有多个字段,并且希望通过优化代码获得所有这些字段的最新值:

select * from
(select * from table_name
order by id,rev desc) temp
group by id 

其他回答

我喜欢使用基于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
)

这将选择组中具有最大值的所有记录,并允许您选择其他列。

解释

这不是纯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数据库上测试。

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

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个连接时,它的性能会受到组大小的严重影响。。。

我喜欢通过按某一列对记录进行排名来做到这一点。在这种情况下,按id对rev值进行分组。rev值越高的排名越低。所以,最高转速的排名为1。

select id, rev, content
from
 (select
    @rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num,
    id, rev, content,
    @prevValue := id
  from
   (select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP,
   (select @rowNum := 1 from DUAL) X,
   (select @prevValue := -1 from DUAL) Y) TEMP
where row_num = 1;

不确定引入变量是否会使整个过程变慢。但至少我没有两次查询YOURTABLE。

我惊讶地发现,没有答案提供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。