获取每组分组的SQL结果的最大值的记录 [英] Get records with max value for each group of grouped SQL results

问题描述

如何获取包含每个分组集最大值的行?

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的解决方案.我知道的所有其他数据库都将引发SQL语法错误，并显示消息"group by子句中未列出未聚合的列"或类似消息.由于此解决方案使用 undocumented 行为，因此较为谨慎的做法可能是包括一个测试，以断言如果将来的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).

这篇关于获取每组分组的SQL结果的最大值的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！