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

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

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

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


当前回答

我喜欢通过按某一列对记录进行排名来做到这一点。在这种情况下,按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。

其他回答

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

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

此解决方案仅从YourTable中进行一次选择,因此速度更快。根据sqlfiddle.com上的测试,它只适用于MySQL和SQLite(用于SQLite删除DESC)。也许可以调整它以适用于我不熟悉的其他语言。

SELECT *
FROM ( SELECT *
       FROM ( SELECT 1 as id, 1 as rev, 'content1' as content
              UNION
              SELECT 2, 1, 'content2'
              UNION
              SELECT 1, 2, 'content3'
              UNION
              SELECT 1, 3, 'content4'
            ) as YourTable
       ORDER BY id, rev DESC
   ) as YourTable
GROUP BY id

我会用这个:

select t.*
from test as t
join
   (select max(rev) as rev
    from test
    group by id) as o
on o.rev = t.rev

子查询SELECT可能不太有效,但在JOIN子句中似乎是可用的。我不是优化查询的专家,但我在MySQL、PostgreSQL和FireBird上都做过尝试,而且效果非常好。

您可以在多个联接中使用此模式,也可以使用WHERE子句。这是我的工作示例(用表格“firmy”解决与您相同的问题):

select *
from platnosci as p
join firmy as f
on p.id_rel_firmy = f.id_rel
join (select max(id_obj) as id_obj
      from firmy
      group by id_rel) as o
on o.id_obj = f.id_obj and p.od > '2014-03-01'

这是在有十几张唱片的桌子上被要求的,在不太强的机器上只需要不到0.01秒的时间。

我不会使用IN子句(如上所述)。IN用于常量的短列表,而不是构建在子查询上的查询过滤器。这是因为in中的子查询是针对每个扫描的记录执行的,这可能会使查询花费很长时间。

这些答案对我来说都不管用。

这就是对我有用的。

with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max

解释

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