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

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

idrev内容
11…
21…
12…
13…

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

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

1844389 次浏览
小开
最佳答案

乍一看…

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

SELECT id, MAX(rev)FROM YourTableGROUP BY id

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

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

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

实际上，Stack Overflow社区创建了一个标签来处理这样的问题：。

基本上，你有两种方法来解决这个问题：

使用简单的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) revFROM YourTableGROUP BY id) b ON a.id = b.id AND a.rev = b.rev

左连接与self，调整连接条件和过滤器

在这种方法中，你离开加入表格本身。平等进入group-identifier。然后，2个聪明的举动：

  1. 第二个连接条件是左侧值小于右侧值
  2. 当你执行第1步时，实际具有最大值的行将在右侧具有NULL（它是LEFT JOIN，记得吗？）。然后，我们过滤连接的结果，仅显示右侧为NULL的行。

所以你最终会得到：

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

结论

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

如果您有两行max-value-in-group表示group-identifier，则这两行都将在两种方法的结果中。

这两种方法都SQLANSI兼容，因此，将与您最喜欢的RDBMS一起工作，无论其“味道”如何。

这两种方法都是性能友好的，但因人而异（RDBMS、DB结构、索引等）。因此，当您选择一种方法而不是另一种方法时，基准。并确保您选择了对您最有意义的一种。

小开

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

你可以用IN来做试试这个：

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

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

小开

像这样的东西？

SELECT yourtable.id, rev, contentFROM yourtableINNER JOIN (SELECT id, max(rev) as maxrevFROM yourtableGROUP BY id) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)
小开

我不能保证性能，但这是一个受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_revFROM  (SELECT id,CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_colFROM yourtable)GROUP BY id

打包开始时强制rev列为已知字符长度的数字，而不管rev的值如何，因此例如

  • 3.2变为1003.201
  • 57变成1057.001
  • 923.88变为1923.881

如果你做得对，两个数字的字符串比较应该产生与两个数字的数字比较相同的“max”，并且很容易使用子字符串函数转换回原始数字（几乎无处不在）。

小开

这个怎么样：

SELECT all_fields.*FROM (SELECT id, MAX(rev) FROM yourtable GROUP BY id) AS max_recsLEFT OUTER JOIN yourtable AS all_fieldsON max_recs.id = all_fields.id
小开

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

select yt.id, yt.rev, yt.contentsfrom YourTable ytwhere rev =(select max(rev) from YourTable st where yt.id=st.id)

在（id， rev）上有一个索引会使子查询几乎成为一个简单的查找…

以下是与@AdrianCarneiro的答案（子查询，左连接）中的解决方案的比较，基于MySQL测量，InnoDB表为~100万记录，组大小为：1-3。

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

最后要注意的是，由于leftjoin在组中创建n*（n+1）/2个连接，它的性能可能会受到组大小的严重影响…

小开

这个解决方案只从YourTable中选择一个，因此速度更快。根据sqlfiddle.com.的测试，它只适用于MySQL和SQLite（对于SQLite删除DESC）。也许可以调整它以适用于我不熟悉的其他语言。

SELECT *FROM ( SELECT *FROM ( SELECT 1 as id, 1 as rev, 'content1' as contentUNIONSELECT 2, 1, 'content2'UNIONSELECT 1, 2, 'content3'UNIONSELECT 1, 3, 'content4') as YourTableORDER BY id, rev DESC) as YourTableGROUP BY id
小开

不是mySQL，但对于其他人发现这个问题并使用SQL，解决#09;每群最大n个&#09;" rel="tag">每群最大n个问题的另一种方法是在MS中使用#0SQL

WITH DocIds AS (SELECT DISTINCT id FROM docs)
SELECT d2.id, d2.rev, d2.contentFROM DocIds d1CROSS APPLY (SELECT Top 1 * FROM docs dWHERE d.id = d1.idORDER BY rev DESC) d2

这是SqlFiddle中的一个例子

小开

由于这是关于这个问题的最受欢迎的问题，我也会在这里重新发布另一个答案：

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

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

这个问题中的请相信用户波希米亚人的回答为这个问题提供了如此简洁和优雅的答案。

编辑：虽然这个解决方案对很多人都有效，但从长远来看可能不稳定，因为MySQL不保证GROUP BY语句会为不在GROUP BY列表中的列返回有意义的值。所以使用这个解决方案需要自担风险！

小开

我喜欢使用基于NOT EXIST的解决方案来解决这个问题：

SELECTid,rev-- you can select other columns hereFROM YourTable tWHERE NOT EXISTS (SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev)

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

小开

注意：在MySQL 8天以上的时间里，我可能不会再推荐这个了。多年没有使用过它了。

我很少看到提到的第三个解决方案是MySQL特定的，看起来像这样：

SELECT id, MAX(rev) AS rev, 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_contentFROM t1GROUP BY id

是的，它看起来很糟糕（转换为字符串并返回等），但根据我的经验，它通常比其他解决方案更快。也许这只是针对我的用例，但我已经在具有数百万条记录和许多唯一id的表上使用过它。也许是因为MySQL在优化其他解决方案方面非常糟糕（至少在我想出这个解决方案的5.0天内）。

重要的一点是GROUP_CONCAT可以构建的字符串有一个最大长度。您可能希望通过设置group_concat_max_len变量来提高此限制。请记住，如果您有大量行，这将是缩放的限制。

无论如何，如果您的内容字段已经是文本，上述内容将不直接起作用。在这种情况下，您可能希望使用不同的分隔符，例如\0。您还会更快地遇到group_concat_max_len限制。

小开

这里有个不错的方法

使用以下代码：

with temp as  (select count(field1) as summ , field1from table_namegroup by field1 )select * from temp where summ = (select max(summ) from temp)
小开

我会用这个：

select t.*from test as tjoin(select max(rev) as revfrom testgroup by id) as oon o.rev = t.rev

子查询SELECT可能不是太有效，但在JOIN子句中似乎是可用的。我不是优化查询的专家，但我尝试过MySQL、PostgreSQL、FireBird，它确实工作得很好。

您可以在多个连接和WHERE子句中使用此模式。这是我的工作示例（用表“fimy”解决与您的问题相同的问题）：

select *from platnosci as pjoin firmy as fon p.id_rel_firmy = f.id_reljoin (select max(id_obj) as id_objfrom firmygroup by id_rel) as oon o.id_obj = f.id_obj and p.od > '2014-03-01'

它是问表有青少年thusands的记录，它需要不到0,01秒在真的不太强的机器。

我不会使用IN子句（正如上面提到的那样）。IN是用来处理短列表的，而不是构建在子查询上的查询过滤器。这是因为IN中的子查询是针对每个扫描的记录执行的，这可能会使查询花费很长时间。

小开

我喜欢通过按某个列对记录进行排名来做到这一点。在这种情况下，排名rev的值由id分组。那些排名较高的rev将排名较低。所以最高的rev将排名为1。

select id, rev, contentfrom(select@rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num,id, rev, content,@prevValue := idfrom(select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP,(select @rowNum := 1 from DUAL) X,(select @prevValue := -1 from DUAL) Y) TEMPwhere row_num = 1;

不确定引入变量是否会使整个过程变慢。但至少我不会两次查询YOURTABLE。

小开

如果您在选择语句中有许多字段，并且您希望通过优化代码为所有这些字段提供最新值：

select * from(select * from table_nameorder by id,rev desc) tempgroup by id
小开

以相反的顺序对rev字段进行排序，然后按id分组，该id给出每个分组的第一行，这是具有最高rev值的行。

SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;

使用以下数据在http://sqlfiddle.com/中测试

CREATE TABLE table1(`id` int, `rev` int, `content` varchar(11));
INSERT INTO table1(`id`, `rev`, `content`)VALUES(1, 1, 'One-One'),(1, 2, 'One-Two'),(2, 1, 'Two-One'),(2, 2, 'Two-Two'),(3, 2, 'Three-Two'),(3, 1, 'Three-One'),(3, 3, 'Three-Three');

这在MySql 5.5和5.6中给出了以下结果 

id  rev content1   2   One-Two2   2   Two-Two3   3   Three-Two
小开

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

SELECT a.id, a.rev, a.contentsFROM (SELECT id, rev, contents,ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) ranked_orderFROM YourTable) aWHERE a.ranked_order = 1

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

小开

唯一标识符？是的！唯一标识符！

开发MySQL数据库的最佳方法之一是每个#0#1（源代码MySQL.com）。这允许各种优势，这里无法介绍。这个问题的问题是它的示例有重复的ID。这忽略了唯一标识符的这些巨大优势，同时，熟悉这一点的人也会感到困惑。

最清洁的解决方案

DB小提琴

较新版本的MySQL默认启用ONLY_FULL_GROUP_BY，这里的许多解决方案将在测试中排名第一。

即便如此，我们也可以简单地选择DISTINCT某地、MAX(选择任何其他字段)、(*一些第三字段)等，并且不用担心理解结果或查询的工作原理：

SELECT DISTINCT t1.id, MAX(t1.rev), MAX(t2.content)FROM Table1 AS t1JOIN 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：强制每个员工的顶部排序的Salary行作为返回结果。

请注意，由于OP的问题中的“内容”是“…”，因此无法测试这是否有效。所以，我将其更改为“… a”，“… b”，所以，我们现在实际上可以看到结果是正确的：

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

为什么它是干净的？DISTINCT()、MAX()等都很好地利用了MySQL索引。这会更快。或者，如果您有索引，并且将其与查看所有行的查询进行比较，它会更快。

原始解决方案

禁用ONLY_FULL_GROUP_BY后，我们仍然可以使用useGROUP BY，但我们只在Salary上使用它，而不是id：

SELECT *FROM(SELECT *FROM EmployeeORDER BY Salary DESC)AS employeesubGROUP BY employeesub.Salary;
  • SELECT *：返回所有字段。
  • FROM Employee：表搜索。
  • (SELECT *...)子查询：返回所有人，按工资排序。
  • GROUP BY employeesub.Salary：强制每个员工的顶部排序的Salary行作为返回结果。

唯一行解决方案

注意关系数据库的定义：“表中的每一行都有自己唯一的键。”这意味着，在问题的示例中，id将必须是唯一，在这种情况下，我们可以这样做：

SELECT *FROM EmployeeWHERE Employee.id = 12345ORDER BY Employee.Salary DESCLIMIT 1

希望这是一个解决问题的解决方案，可以帮助每个人更好地了解数据库中发生的事情。

小开

这里有另一个解决方案希望它能帮助别人 

Select a.id , a.rev, a.content from Table1 ainner join(SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev
小开

这些答案都没有为我工作。

这是什么为我工作。

with score as (select max(score_up) from history)select history.* from score, history where history.score_up = score.max
小开 
SELECT *FROM Employeewhere Employee.Salary in (select max(salary) from Employee group by Employe_id)ORDER BY Employee.Salary
小开

这是仅使用具有该字段最大值的字段检索记录的另一种解决方案。这适用于我工作的平台SQL400。在此示例中，字段FIELD5中具有最大值的记录将通过以下SQL语句检索。

SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5FROM MYFILE AWHERE RRN(A) IN(SELECT RRN(B)FROM MYFILE BWHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2ORDER BY B.FIELD5 DESCFETCH FIRST ROW ONLY)
小开

我使用下面的方法来解决我自己的问题。我首先创建了一个临时表并插入每个唯一ID的最大转速值。

CREATE TABLE #temp1(id varchar(20), rev int)INSERT INTO #temp1SELECT a.id, MAX(a.rev) as revFROM(SELECT id, content, SUM(rev) as revFROM YourTableGROUP BY id, content) as aGROUP BY a.idORDER BY a.id

然后，我将这些最大值（#tem1）加入所有可能的id/内容组合。通过这样做，我自然地过滤掉非最大id/内容组合，并为每个组合留下唯一的最大rev值。

SELECT a.id, a.rev, contentFROM #temp1 as aLEFT JOIN(SELECT id, content, SUM(rev) as revFROM YourTableGROUP BY id, content) as b on a.id = b.id and a.rev = b.revGROUP BY a.id, a.rev, b.contentORDER BY a.id
小开

另一种方法是在OVER PARTITION子句中使用MAX()分析函数

SELECT t.*FROM(SELECT id,rev,contents,MAX(rev) OVER (PARTITION BY id) as max_revFROM YourTable) tWHERE 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) rankFROM YourTable) tWHERE t.rank = 1

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

MAX（）解决方案肯定比ROW_NUMBER()解决方案运行得更快，因为MAX()的复杂性是O(n)，而ROW_NUMBER()的复杂性至少是O(n.log(n))，其中n代表表中的记录数！

小开

当您将rev和id组合成MAX()的一个maxRevId值，然后将其拆分回原始值时，您可以在没有连接的情况下进行选择：

SELECT maxRevId & ((1 << 32) - 1) as id, maxRevId >> 32 AS revFROM (SELECT MAX(((rev << 32) | id)) AS maxRevIdFROM YourTableGROUP BY id) x;

当有复杂连接而不是单个表时，这尤其快。使用传统方法，复杂连接将执行两次。

当rev和id是INT UNSIGNED（32位）并且组合值适合BIGINT UNSIGNED（64位）时，上述组合对于位函数很简单。当id和rev大于32位值或由多列组成时，您需要将值组合成例如具有适合MAX()填充的二进制值。

小开

我想，你想要这个吗？

select * from docs where (id, rev) IN (select id, max(rev) as rev from docs group by id order by id)

SQL小提琴：看这里

小开

补充说明

这不是纯SQL。这将使用SQLAlchemy ORM。

我来这里寻找SQLAlchemy的帮助，所以我将使用python/SQLAlchemy版本复制Adrian Carneiro的答案，特别是外部连接部分。

此查询回答以下问题：

“你能把这组记录（基于相同的id）中版本号最高的记录返回给我吗？”

这允许我复制记录，更新它，增加其版本号，并以可以随时间显示更改的方式拥有旧版本的副本。

代码

MyTableAlias = aliased(MyTable)newest_records = appdb.session.query(MyTable).select_from(join(MyTable,MyTableAlias,onclause=and_(MyTable.id == MyTableAlias.id,MyTable.version_int < MyTableAlias.version_int),isouter=True)).filter(MyTableAlias.id  == None,).all()

在PostgreSQL数据库上测试。


提交答案