协慌网

 登录 贡献 社区

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

我有这个文件表（这里是简化版）： 

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

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

目前，我使用while循环中的检查来检测并覆盖结果集中的旧转速。但这是实现结果的唯一方法吗？是不是有SQL解决方案？

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

更新 2
我注意到在添加上述sqlfiddle 之后 ，问题被投票的速度超过了答案的 upvote 率。那不是故意的！小提琴是基于答案，特别是接受的答案。

答案

乍一看...

您只需要具有MAX聚合函数的GROUP BY子句： 

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

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

我刚刚注意到你也需要content栏。

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

实际上，StackOverflow 社区创建一个标记只是为了处理这样的问题： 每个组中最大的 n 。

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

加入简单的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

Left 加入 self，调整连接条件和过滤器

在这种方法中，你自己加入了表。当然，平等在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，DB 结构，索引等）。所以，当你选择一种方法而不是另一种方法时， 基准 。并确保你选择对你最有意义的一个。

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

你可以使用IN试试这个： 

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

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

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

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）中的解决方案的比较，基于使用 InnoDB 表的 MySQL 测量，约 1 百万条记录，组大小为：1-3。

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

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