問題描述
如何獲取包含每個分組集最大值的行?
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結果的最大值記錄的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！