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

Question

我有这个文件表（这里是简化版）：+------+-------+--------------------------------------+| id   | rev   | content                              |+------+-------+--------------------------------------+| 1    | 1     | ...                                  || 2    | 1     | ...                                  || 1    | 2     | ...                                  || 1    | 3     | ...                                  |+------+-------+--------------------------------------+如何为每个id选择一行并且只选择最大转速？使用上面的数据，结果应该包含两行：[1, 3, ...]和[2, 1, ..]。我正在使用MySQL。目前，我在while循环中使用检查来检测并覆盖结果集中的旧转速。但这是实现结果的唯一方法吗？是不是有SQL解决方案？

Answer 1

乍一看...

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

SELECT id, MAX(rev)FROM YourTableGROUP 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.contentsFROM YourTable aINNER 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在右侧（它是a LEFT JOIN，记住吗？）。然后，我们过滤连接结果，仅显示右侧所在的行NULL。

所以你最终得到：

SELECT a.*FROM YourTable aLEFT OUTER JOIN YourTable b    ON a.id = b.id AND a.rev < b.revWHERE b.id IS NULL;

结论

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

如果有两行max-value-in-groupfor group-identifier，则两行都将在结果中。

这两种方法都是SQL ANSI兼容的，因此，无论其“风味”如何，它都可以与您喜欢的RDBMS一起使用。

这两种方法都具有性能友好性，但您的里程可能会有所不同（RDBMS，DB结构，索引等）。所以，当你选择一种方法而不是另一种方法时，基准。并确保你选择对你最有意义的一个。

Answer 2

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

你可以IN 尝试这样做：

SELECT * 

FROM t1 WHERE (id,rev) IN 

( SELECT id, MAX(rev)

  FROM t1

  GROUP BY id

)

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

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) rank          FROM YourTable) a WHERE a.rank = 1

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