SQL 仅选择列上具有最大值的行

我有此表用于文档（此处为简化版）：

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

如何为每个 ID 选择一行，而仅选择最大转速？
根据上述数据，结果应包含两行： [1, 3, ...]和[2, 1, ..] 。我正在使用MySQL 。

目前，我在while循环中使用检查功能来检测并覆盖结果集中的旧版本。但这是获得结果的唯一方法吗？没有SQL解决方案吗？

更新资料
作为答案提示，有一个 SQL 的解决方案，并且这里 sqlfiddle 演示。

更新 2
在添加上述sqlfiddle 之后 ，我注意到问题被投票的速率已经超过答案的投票率。那不是意图！小提琴基于答案，尤其是已接受的答案。

第一眼看去...

您需要做的是带有MAX聚合函数的GROUP BY子句：

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

从来没有那么简单，是吗？

我只是注意到您也需要content列。

在 SQL 中，这是一个非常常见的问题：查找行的整个数据，并在每个组标识符的列中找到某个最大值。我在职业生涯中听到了很多。实际上，这是我在当前工作的技术面试中回答的问题之一。

实际上，是如此普遍，以至于 StackOverflow 社区创建了一个标签来处理这样的问题： great-n-per-group 。

基本上，有两种方法可以解决该问题：

与简单的`group-identifier, max-value-in-group`子查询结合

在这种方法中，您首先要在子查询中找到group-identifier, max-value-in-group即group-identifier, max-value-in-group （已在上面解决）。然后，在group-identifier和max-value-in-group相等的情况下，将表连接到子查询：

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

自我左联接，调整联接条件和过滤器

在这种方法中，您无需将表本身连接起来。平等在group-identifier 。然后，有 2 个明智的举动：

第二个连接条件是左侧值小于右侧值
当您执行步骤 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;

结论

两种方法都带来完全相同的结果。

如果您有两行具有用于group-identifier max-value-in-group ，则两种方法的结果都将出现在这两行中。

两种方法都与 SQL ANSI 兼容，因此，无论其 “味道” 如何，都将与您喜欢的 RDBMS 一起使用。

两种方法都对性能很友好，但是您的工作量可能会有所不同（RDBMS，数据库结构，索引等）。因此，当您选择一种方法而不是另一种方法时，请选择基准。并确保选择最有意义的一种。

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

您可以使用IN尝试以下操作：

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

在我看来，它并不那么复杂... 更易于阅读和维护。

我很惊讶没有答案提供 SQL 窗口函数解决方案：

SELECT a.id, a.rev, a.contents
  FROM (SELECT id, rev, contents,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
          FROM YourTable) a
 WHERE a.rank = 1

在 SQL 标准 ANSI / ISO 标准 SQL：2003 中添加，并在以后通过 ANSI / ISO 标准 SQL：2008 进行了扩展，窗口（或窗口）功能现在可用于所有主要供应商。有更多类型的排名函数可用于处理平局问题： RANK, DENSE_RANK, PERSENT_RANK 。

另一个解决方案是使用相关子查询：

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 的答案（子查询，leftjoin）中的解决方案的比较，该解决方案基于 MySQL 的测量结果，其中 InnoDB 表具有约 100 万条记录，组大小为 1-3。

虽然对于全表扫描，子查询 / leftjoin / 相关时间相互关联为 6/8/9，但在直接查找或批处理（ id in (1,2,3) ）时，子查询要比其他查询慢得多（由于重新运行子查询）。但是，我无法区分左连接和相关解决方案的速度。

最后一点，当 leftjoin 在组中创建 n *（n + 1）/ 2 个连接时，其性能可能会受到组大小的严重影响。

我不能保证性能，但这是受 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.201
57 变成 1057.001
923.88 变为 1923.881

如果操作正确，则两个数字的字符串比较应产生与两个数字的数字比较相同的 “最大值”，并且使用 substring 函数（以一种形式或另一种形式可用）很容易转换回原始数字到处）。

我认为这是最简单的解决方案：

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;

SELECT * ：返回所有字段。
FROM Employee ：已搜索表格。
(SELECT *...)子查询：返回所有人员，按薪水排序。
GROUP BY employeesub.Salary ：强制将每位员工的排在最前的 Salary 行作为返回结果。

如果您碰巧只需要一行，那就更简单了：

SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1

我还认为，分解，理解和修改其他目的是最容易的：

ORDER BY Employee.Salary DESC ：按薪水ORDER BY Employee.Salary DESC结果，薪水最高。
LIMIT 1 ：仅返回一个结果。

理解这种方法，解决这些类似问题中的任何一个都变得微不足道：获得薪水最低的员工（将DESC更改为ASC ），获得收入最高的十个员工（将LIMIT 1更改为LIMIT 10 ），通过另一个字段进行排序（更改ORDER BY Employee.Salary到ORDER BY Employee.Commission等）。

像这样吗

SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
    SELECT id, max(rev) as maxrev FROM yourtable
    WHERE yourtable
    GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)

完成这项工作的另一种方法是在 OVER PARTITION 子句中使用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() OVER PARTITION 解决方案是

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表示表中的记录数！

我喜欢针对此问题使用基于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
)

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

由于这是关于此问题的最受欢迎的问题，因此我还将在此处重新发布另一个答案：

看起来有更简单的方法可以做到这一点（但仅限于 MySQL ）：

select *
from (select * from mytable order by id, rev desc ) x
group by id

请将此问题 的用户 Bohemian 答案归因于为该问题提供了这样简洁明了的答案。

编辑：尽管此解决方案对许多人都有效，但从长远来看可能并不稳定，因为 MySQL 不保证 GROUP BY 语句将为不在 GROUP BY 列表中的列返回有意义的值。因此，使用此解决方案需您自担风险！

答案

第一眼看去...

从来没有那么简单，是吗？

与简单的group-identifier, max-value-in-group子查询结合

自我左联接，调整联接条件和过滤器

结论

与简单的`group-identifier, max-value-in-group`子查询结合