Rufen Sie den Maximalwertdatensatz in jedem gruppierten SQL-Ergebnis ab

Question

Wie erhalte ich die Zeile mit dem Maximalwert für jede Gruppierung?

Ich habe ein paar übermäßig komplexe Varianten gesehen, aber keine hat eine gute Antwort gegeben. Ich habe das einfachste Beispiel ausprobiert:

Wie erhalten Sie anhand der folgenden Tabelle, die Spalten für Personen, Gruppen und Alter enthält, die älteste Person in jeder Gruppe? (Ein Unentschieden innerhalb einer Gruppe sollte das erste Ergebnis in alphabetischer Reihenfolge ergeben)

Personengruppe |
---
Bob |. 1 |. 32
Jill |. 1 |
Shawn|. 1 |
Jake |. 2 |
Paul |. 2 |
Laura|. 2 |. 39

Gewünschter Ergebnissatz:

Shawn 1 |
Laura |. 2 |. 39

Answer 1

正确的解决方案是：

SELECT o.*
FROM `Persons` o                    # 'o' from 'oldest person in group'
  LEFT JOIN `Persons` b             # 'b' from 'bigger age'
      ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL                 # bigger age not found

它是如何工作的：

它将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》中有详细解释。

Answer 2

在mysql中有一种超级简单的方法可以做到这一点：

select * 
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`

这个方法有效是因为在mysql中，你可以不对非group by列进行聚合，这种情况下mysql只返回第一行。解决方法是首先按照你想要的顺序对数据进行排序，然后按照你想要的列进行分组。

你避免了复杂的子查询尝试找到max()等等的问题，也避免了当有多个具有相同最大值的行时返回多行的问题（其他答案会这样做）。

注意：这是一种仅适用于mysql的解决方案。我知道的所有其他数据库都会抛出一个SQL语法错误，错误信息为“非聚合列未在group by子句中列出”或类似的错误。因为这个解决方案使用了未记录的行为，更谨慎的人可能希望包含一个测试来确保它在MySQL的未来版本更改此行为时仍然有效。

版本5.7更新：

自5.7版本起，sql-mode设置默认包含ONLY_FULL_GROUP_BY，所以要使其工作，你必须不使用此选项（编辑服务器的选项文件以删除此设置）。