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 社区创建了一个标签来处理这样的问题:

基本上,有两种方法可以解决该问题:

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

在这种方法中,您首先要在子查询中找到group-identifier, max-value-in-groupgroup-identifier, max-value-in-group (已在上面解决)。然后,在group-identifiermax-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. 第二个连接条件是左侧值小于右侧值
  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.SalaryORDER 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 列表中的列返回有意义的值。因此,使用此解决方案需您自担风险!