如何获取包含每个分组的最大值的行?
我看到了一些过于复杂的变体,但没有一个给出了好的答案。我尝试了最简单的例子:
给定下面的表格,包含人员、分组和年龄列,如何获取每个分组中年龄最大的人员?(在一个分组内的平局应该给出字母顺序中的第一个结果)
Person | Group | Age --- Bob | 1 | 32 Jill | 1 | 34 Shawn| 1 | 42 Jake | 2 | 29 Paul | 2 | 36 Laura| 2 | 39
期望的结果集:
Shawn | 1 | 42 Laura | 2 | 39
正确的解决方案是:
它是如何工作的:
它将
o
中的每一行与具有相同Group
列值和较大Age
列值的b
中的所有行进行匹配。任何o
中的行如果在Age
列中没有其组中的最大值,则会与b
中的一行或多行匹配。LEFT JOIN
使其将组中的最年长的人(包括那些独自一人的人)与来自b
的一行NULL
进行匹配('组中没有更大的年龄')。使用
INNER JOIN
会使这些行不匹配,并且它们会被忽略。WHERE
子句仅保留从b
中提取的字段中具有NULL
的行。它们是每个组中最年长的人。进一步阅读
这个解决方案和许多其他解决方案在书籍《SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming》中有详细解释。
在mysql中有一种超级简单的方法可以做到这一点:
这个方法有效是因为在mysql中,你可以不对非group by列进行聚合,这种情况下mysql只返回第一行。解决方法是首先按照你想要的顺序对数据进行排序,然后按照你想要的列进行分组。
你避免了复杂的子查询尝试找到
max()
等等的问题,也避免了当有多个具有相同最大值的行时返回多行的问题(其他答案会这样做)。注意:这是一种仅适用于mysql的解决方案。我知道的所有其他数据库都会抛出一个SQL语法错误,错误信息为“非聚合列未在group by子句中列出”或类似的错误。因为这个解决方案使用了未记录的行为,更谨慎的人可能希望包含一个测试来确保它在MySQL的未来版本更改此行为时仍然有效。
版本5.7更新:
自5.7版本起,
sql-mode
设置默认包含ONLY_FULL_GROUP_BY
,所以要使其工作,你必须不使用此选项(编辑服务器的选项文件以删除此设置)。