获取每组分组 SQL 结果的最大值记录
如何获取包含每个分组集最大值的行?
How do you get the rows that contain the max value for each grouped set?
我在这个问题上看到了一些过于复杂的变体，但都没有一个好的答案.我试图把最简单的例子放在一起:
I've seen some overly-complicated variations on this question, and none with a good answer. I've tried to put together the simplest possible example:
给定下面这样的表格，其中包含人员、组和年龄列，您将如何获得每个组中最年长的人?(组内的平局应该给出第一个字母结果)
Given a table like that below, with person, group, and age columns, how would you get the oldest person in each group? (A tie within a group should give the first alphabetical result)
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
推荐答案
在mysql中有一个超级简单的方法:
There's a super-simple way to do this in mysql:
select *
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`
这是可行的，因为在 mysql 中，您可以不聚合非分组列，在这种情况下，mysql 只返回 first 行.解决方案是首先对数据进行排序，这样对于每个组，您想要的行是第一位的，然后按您想要的值的列进行分组.
This works because in mysql you're allowed to not aggregate non-group-by columns, in which case mysql just returns the first row. The solution is to first order the data such that for each group the row you want is first, then group by the columns you want the value for.
您避免了尝试查找 max() 等的复杂子查询，以及当有多个具有相同最大值的行时返回多行的问题(就像其他答案一样)
You avoid complicated subqueries that try to find the max() etc, and also the problems of returning multiple rows when there are more than one with the same maximum value (as the other answers would do)
注意:这是一个mysql-only解决方案.我知道的所有其他数据库都会引发 SQL 语法错误，并显示消息非聚合列未列在 group by 子句中".或类似的.由于此解决方案使用未记录行为，更谨慎的人可能希望包含一个测试，以断言如果未来版本的 MySQL 更改此行为，它仍然工作.
Note: This is a mysql-only solution. All other databases I know will throw an SQL syntax error with the message "non aggregated columns are not listed in the group by clause" or similar. Because this solution uses undocumented behavior, the more cautious may want to include a test to assert that it remains working should a future version of MySQL change this behavior.
从 5.7 版开始，sql-mode 设置包括 ONLY_FULL_GROUP_BY 默认情况下，因此要使这项工作您必须不拥有此选项(编辑服务器的选项文件以删除此设置).
Since version 5.7, the sql-mode setting includes ONLY_FULL_GROUP_BY by default, so to make this work you must not have this option (edit the option file for the server to remove this setting).
相关文章