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

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

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

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


当前回答

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

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

其他回答

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

SELECT *
FROM Employee
where Employee.Salary in (select max(salary) from Employee group by Employe_id)
ORDER BY Employee.Salary

我不能保证它的性能,但这里有一个受Microsoft Excel限制启发的技巧。它有一些好的特点

好东西

即使有平局,它也应该强制返回一个“最大记录”(有时有用)它不需要联接

接近,接近

它有点难看,需要您了解rev列的有效值范围。假设我们知道rev列是一个介于0.00和999之间的数字,包括小数,但小数点右侧只有两位数字(例如,34.17是一个有效值)。

这件事的要点是,通过将主比较字段与所需数据串接/打包,可以创建一个单独的合成列。通过这种方式,您可以强制SQL的MAX()聚合函数返回所有数据(因为它已打包到一列中)。然后您必须解压缩数据。

下面是上面用SQL编写的示例的外观

SELECT id, 
       CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev,
       SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev 
FROM  (SELECT id, 
       CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col
       FROM yourtable
      ) 
GROUP BY id

包装开始时,将rev列强制为已知字符长度的数字,而不考虑rev的值,例如

3.2变为1003.20157变为1057.001923.88变为1923.881

如果你做得对,两个数字的字符串比较应该产生与两个数字数字比较相同的“最大值”,并且很容易使用substring函数转换回原始数字(这在任何地方都可以使用一种或另一种形式)。

我的偏好是使用尽可能少的代码。。。

您可以使用IN试试看:

SELECT * 
FROM t1 WHERE (id,rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)

在我看来,这不那么复杂。。。易于阅读和维护。

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