仅选择具有列最大值的行的SQL select语句

Question

我有一个文档表格（简化版本如下）：

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

怎样才能每个id只选取最大rev的一行？
给定上述的数据，结果应该包含两行： [1, 3, ...] 和 [2, 1, ..]。我使用的是MySQL。

目前我在while 循环中检查并覆盖结果集中旧的rev。但这是实现结果的唯一方法吗？难道没有SQL的解决方案吗？

- Majid Fouladpour

---

你需要该行对应的“content”字段吗？ - Mark Byers

@MarkByers 我已经编辑了我的答案以符合 OP 的需求。既然我在这个问题上，我决定写一个更全面的关于 [tag:greatest-n-per-group] 主题的答案。 - Adriano Carneiro

这是一个常见的 [tag:greatest-n-per-group] 问题，已经有经过充分测试和优化的解决方案（https://dev59.com/xGoy5IYBdhLWcg3wJKoq）。我更喜欢 Bill Karwin 的左连接解决方案（https://dev59.com/xGoy5IYBdhLWcg3wJKoq#8749095）（原始帖子：https://dev59.com/VHVD5IYBdhLWcg3wAWkO#123481）。请注意，这个常见问题的许多解决方案出奇地可以在最官方的来源之一，**MySQL 手册**中找到！请参阅常见查询示例::持有某列分组行的组内最大值。 - Tomas

重复的检索每个分组中最后一条记录。 - Tomas

在我看来，SQLite的做法是正确的，因为它首先使用了数据，所以我对其他数据库不自动使用同一行数据进行聚合函数的做法感到困惑和恼火。 - Michael

Answer 1

乍一看...

你只需要使用带有MAX聚合函数的GROUP BY子句：

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

事情往往没有那么简单，不是吗？

我刚注意到你需要content列。

这是SQL中非常常见的问题：针对某个组标识符查找某一列中最大值的整行数据。在我的职业生涯中，我经常听到这样的问题。实际上，在我目前工作的技术面试中，这就是其中一个我回答过的问题。

这个问题实际上非常普遍，以至于Stack Overflow社区创建了一个特殊的标签来处理类似的问题：greatest-n-per-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

使用自连接进行左连接，并调整连接条件和过滤器

在这种方法中，您将表与自身左连接。相等性放置在组标识符中。然后，有两个聪明的举措:

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、数据库结构、索引等）。当您选择其中一种方法时，请进行基准测试，确保您选择的方法最符合您的需求。

- Adriano Carneiro

---

这是一个非常糟糕的想法，因为你想要最大化的领域可能是双精度浮点数，而比较双精度浮点数是否相等是不确定的。我认为只有 O(n^2) 的算法适用于这里。 - mk3009hppw

@Adriano 如果有一个额外的列 user_id，并且您想将结果限制在该 user_id 上，这该怎么做呢？我认为筛选应该尽早发生，以避免将与后来会被放弃的无关 user_id 相关联的东西连接在一起。 - binoculars

我不确定这两种方法是否会“带来完全相同的结果”：我认为第二种方法将保留rev字段为空的记录（它们在连接中没有匹配项），但第一种方法将不会保留它们（它们的rev不是最大值，因此未被选中）。 - a3nm

另一种方法是使用窗口函数，它们似乎提供更好的性能。我会这样做： SELECT DISTINCT id, MAX(rev) OVER (PARTITION BY id), FIRST_VALUE(content) OVER (PARTITION BY id ORDER BY rev DESC) FROM YourTable - Marcos

比较双精度浮点数是否相等是完全确定的，虽然认为它不确定是一个常见的误解。人们通常所说的是，在精确计算浮点数时（可能仅仅是像0.1 + 0.2这样简单的计算），由于四舍五入的原因，可能无法准确返回所期望的结果（如0.3），或者在比较具有不同精度的数值类型时会出现意外行为。但在此情况下这两种情况都不会发生。 - Ilmari Karonen

Answer 2

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

你可以使用 IN 来实现它，请尝试这样做：

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

我认为这样不太复杂...更易于阅读和维护。

- Kevin Burton

---

好奇 - 我们可以在哪种数据库引擎中使用这种WHERE子句？这在SQL Server中不受支持。 - Kash

Oracle和MySQL（对于其他数据库不确定，抱歉）。 - Kevin Burton

也适用于 PostgreSQL。 - lcguida

已确认在DB2中可工作。 - coderatchet

不支持SQLite。 - Marcel Pfeiffer

Answer 3

我惊讶地发现没有提供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 Standard SQL：2003中新增的窗口（或窗口）函数已经被所有主要供应商采用，并在后来扩展的ANSI / ISO Standard SQL：2008中得到了扩展。现在有更多类型的排名函数可用于处理平局问题： RANK，DENSE_RANK，PERSENT_RANK 。

- topchef

---

直觉是一个棘手的问题。我发现它比其他答案更具直观性，因为它建立了明确的数据结构来回答问题。但是，再次强调，直觉是偏见的另一面... - topchef

这可能适用于MariaDB 10.2和MySQL 8.0.2，但在此之前不行。 - Rick James

应优先考虑使用窗口函数的方法，因为它更简单易懂。 - Mark Andersen

与相关查询（性能杀手）或其他聚合函数相比，这是更有效的方法。现在应该将其标记为已接受的答案。 - Paramvir Singh Karwal

我认为你不能在代码的第3行使用rank作为字段名，至少在mysql 8.0.29中不行。rank是一个保留字，你必须使用其他名称，比如ranked_order或者其他你想要的名称。 - deweydb

Answer 4

还有一种解决方案是使用相关子查询：

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 答案中解决方案（子查询、左连接）的比较，基于使用 InnoDB 表的 MySQL 测试结果，该表包含约 100 万条记录，组大小为：1-3。

对于全表扫描，子查询/左连接/相关定时关系为6/8/9，但是当涉及直接查找或批量 (id in (1,2,3)) 时，子查询比其他方法慢得多 (由于对子查询进行重新运行)。然而，我无法区分左连接和相关解决方案的速度。

最后注意一点，因为左连接在组内创建了 n*(n+1)/2 个连接，所以它的性能可能会受到组大小的严重影响...

- Vajk Hermecz

---

这个方法很难理解。我无法独立运行子查询，因为它引用了外部查询。看起来子查询每次只返回一个单一的值，但是根据维基百科，“子查询可能会针对外部查询处理的每一行分别计算一次。”在子查询中，yt.id必须为该行生成单个值？最终，对于每一行，子查询似乎都会获取该id的rev的最大值。子查询在查询执行过程中产生不同结果的想法似乎将我们带入了深处。 - Mark E.

@dolmen，注意内部选择在表中的每一行都会被评估。您假设它只被评估一次，并且使用单个值。 - Vajk Hermecz

@VajkHermecz 你是对的。我删除了我的评论。 - dolmen

不喜欢相关子查询 - siggi_pop

这对于旧版本的MySql非常有用（max()技巧选择第一行，而不是最大值所在的行） - Marco Sulla

Answer 5

我不能保证性能，但这里有一个受到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

如果你做得正确，两个数字之间的字符串比较应该产生相同的“max”，并且很容易使用子字符串函数（几乎在任何形式上都可以使用）转换回原始数字。

- David Foster

Answer 6

唯一标识符？当然！唯一标识符！

开发 MySQL 数据库的最佳方法之一是 将每个 id 设为自动增量（参考来源自 MySQL.com）。这样可以带来许多优势，涉及到的内容太多，在此无法详述。问题在于这个问题的示例具有重复的 id。这不仅忽略了唯一标识符的巨大优势，同时也会使那些已经熟悉这个问题的人感到困惑。

最简洁的解决方案

DB Fiddle

较新版本的 MySQL 默认启用了 ONLY_FULL_GROUP_BY，因此这里的许多解决方案都会在测试中失败。

即使如此，我们可以简单地选择 DISTINCT 某个唯一字段、MAX( 其他要选择的字段 )、( *第三个字段 ) 等，不必担心结果或查询工作原理：

SELECT DISTINCT t1.id, MAX(t1.rev), MAX(t2.content)
FROM Table1 AS t1
JOIN Table1 AS t2 ON t2.id = t1.id AND t2.rev = (
    SELECT MAX(rev) FROM Table1 t3 WHERE t3.id = t1.id
)
GROUP BY t1.id;

• SELECT DISTINCT Table1.id, MAX(Table1.rev), MAX(Table2.content) ：返回某些字段的DISTINCT值，另一些字段的MAX()值。最后一个MAX()是冗余的，因为我知道只有一行数据，但查询需要这样写。
• FROM Employee：在该表中进行搜索。
• JOIN Table1 AS Table2 ON Table2.rev = Table1.rev：将第二个表格与第一个表格连接起来，因为我们需要获得MAX(table1.rev)的评论。
• GROUP BY Table1.id：强制返回每个员工的顶部行工资结果。

请注意，由于原问题中“content”是“...”，无法测试它是否有效。因此，我将其更改为“..a”，“..b”，现在我们可以看到结果是正确的：

id  max(Table1.rev) max(Table2.content)
1   3   ..d
2   1   ..b

为什么会更快呢？ DISTINCT()，MAX（）等都可以很好地利用MySQL索引。如果你建立了索引，并将其与查看所有行的查询进行比较，这将会更快。

原始解决方案

禁用ONLY_FULL_GROUP_BY后，我们仍然可以使用GROUP BY，但这时我们只对Salary进行分组，而不是id：

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

• SELECT *：返回所有字段。
• FROM Employee：所搜索的表格。
• (SELECT *...)子查询：返回按薪水排序的所有人员。
• GROUP BY employeesub.Salary：强制每个员工的按薪水排序后的最高行成为返回结果。

唯一行解决方案

请注意关系数据库的定义：“表中的每一行都有其自己独特的键值。”这意味着，在问题的示例中，id必须是唯一的，如果是这种情况，我们可以直接：

SELECT *
FROM Employee
WHERE Employee.id = 12345
ORDER BY Employee.Salary DESC
LIMIT 1

希望这个解决方案能够解决问题，并帮助每个人更好地理解数据库中正在发生的事情。

- HoldOffHunger

Answer 7

像这样的吗？

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

- Marc B

---

不使用连接符的行不行？ - Majid Fouladpour

如果它们能正常工作，那也是可以的。 - Marc B

这似乎是最快的一个（带有适当的索引）。 - Salman A

那个 ON 的另一个缺少子对象让我感到困惑！ - Gwen Au

Answer 8

另一种处理方法是在 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 

这两个 SELECT 在 Oracle 10g 上运行良好。

MAX() 解决方案肯定比 ROW_NUMBER() 解决方案更快，因为 MAX() 的复杂度是 O(n)，而 ROW_NUMBER() 的复杂度至少为 O(n.log(n))，其中 n 表示表中的记录数！

- schlebe

---

第一个查询是完美的，大多数SO帖子缺乏这种讨论。它的性能高效，在获取更多列时非常有用。大多数其他解决方案都是关于获取一列的最大值，而不是每个组具有10行的多行多列。谢谢。 - sanpat

与所有其他解决方案相比，本解决方案表现最佳。针对我的使用情况，在成千上万的分区和数以百万计的记录下，速度几乎快了9倍。 - Ali Sarchami

可以在MySQL 8和SQLite中快速运行，而且MAX()确实是最佳选项。 - wizzard0

Answer 9

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

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

- Bulat

---

通常而言，像这样使用“yes, not exists”比左连接更受欢迎。在早期版本的SQL Server中，它速度更快，尽管我认为现在已经没有区别了。我通常会使用SELECT 1而不是SELECT *，同样是因为在早期版本中它更快。 - EGP

在MySQL中，至少对于EXISTS子查询，SELECT中的列会被忽略。因此，您在那里写什么并不重要。 - rjh

似乎是最简单、最自包含的方法。类似于“in”，但没有分组和函数。在我的使用情况下，它似乎很快。简单性尤其重要，特别是在使用ORM时，在这种情况下，它只是可以与其他条件链接的另一个条件，不像使用连接时那样复杂化查询结构。 - akostadinov

Answer 10

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

- guru008