获取每组分组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 中，您可以不聚合非 group-by 列，在这种情况下，mysql 只返回 第一 行.解决方案是首先对数据进行排序，以便对于每个组，您想要的行在前，然后按您想要的值的列进行分组.

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 子句中"；或类似.由于此解决方案使用未记录的行为，因此更加谨慎的人可能希望包含一个测试，以断言如果未来的 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屋！