MySQL 获取每组分组SQL结果的最大值记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12102200/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get records with max value for each group of grouped SQL results
提问by Yarin
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
Desired result set:
期望的结果集：
Shawn | 1 | 42
Laura | 2 | 39
采纳答案by Bohemian
There's a super-simple way to do this in mysql:
在 mysql 中有一种非常简单的方法可以做到这一点：
select *
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`
This works because in mysql you're allowed to notaggregate non-group-by columns, in which case mysql just returns the firstrow. 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.
这是有效的，因为在 mysql 中，您不能聚合非 group-by 列，在这种情况下，mysql 只返回第一行。解决方案是首先对数据进行排序，以便对于每个组，您想要的行在前，然后按您想要值的列进行分组。
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)
您避免了尝试查找max()等的复杂子查询，以及当有多个具有相同最大值的行时返回多行的问题（就像其他答案一样）
Note:This is a mysql-onlysolution. 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 undocumentedbehavior, the more cautious may want to include a test to assert that it remainsworking should a future version of MySQL change this behavior.
注意：这是一个仅限 mysql 的解决方案。我知道的所有其他数据库都会抛出 SQL 语法错误，并显示消息“非聚合列未列在 group by 子句中”或类似消息。因为此解决方案使用未记录的行为，所以更谨慎的人可能希望包含一个测试，以断言如果未来版本的 MySQL 更改此行为，它仍然可以工作。
Version 5.7 update:
5.7 版更新：
Since version 5.7, the sql-modesetting includes ONLY_FULL_GROUP_BYby default, so to make this work you must nothave this option (edit the option file for the server to remove this setting).
从 5.7 版开始，该sql-mode设置ONLY_FULL_GROUP_BY默认包括在内，因此要使其工作，您必须没有此选项（编辑服务器的选项文件以删除此设置）。
回答by axiac
The correct solution is:
正确的解决办法是：
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
How it works:
这个怎么运作：
It matches each row from owith all the rows from bhaving the same value in column Groupand a bigger value in column Age. Any row from onot having the maximum value of its group in column Agewill match one or more rows from b.
它将来自的每一行o与来自b在 column 中具有相同值且在 column 中Group具有更大值的所有行相匹配Age。列中o没有其组最大值的任何行都Age将匹配来自 的一行或多行b。
The LEFT JOINmakes it match the oldest person in group (including the persons that are alone in their group) with a row full of NULLs from b('no biggest age in the group').
Using INNER JOINmakes these rows not matching and they are ignored.
这LEFT JOIN使得它匹配组中最年长的人（包括他们组中单独的人）与一行完整的NULLs from b（“组中没有最大的年龄”）。
使用INNER JOIN使这些行不匹配并被忽略。
The WHEREclause keeps only the rows having NULLs in the fields extracted from b. They are the oldest persons from each group.
该WHERE子句仅保留NULL从 提取的字段中具有s的行b。他们是每个群体中年龄最大的人。
Further readings
进一步阅读
This solution and many others are explained in the book SQL Antipatterns: Avoiding the Pitfalls of Database Programming
此解决方案和许多其他解决方案在SQL 反模式：避免数据库编程的陷阱一书中进行了解释
回答by Michael Berkowski
You can join against a subquery that pulls the MAX(Group)and Age. This method is portable across most RDBMS.
您可以加入一个拉取MAX(Group)and的子查询Age。这种方法在大多数 RDBMS 中是可移植的。
SELECT t1.*
FROM yourTable t1
INNER JOIN
(
SELECT `Group`, MAX(Age) AS max_age
FROM yourTable
GROUP BY `Group`
) t2
ON t1.`Group` = t2.`Group` AND t1.Age = t2.max_age;
回答by Igor Kulagin
My simple solution for SQLite (and probably MySQL):
我的 SQLite（可能还有 MySQL）的简单解决方案：
SELECT *, MAX(age) FROM mytable GROUP BY `Group`;
However it doesn't work in PostgreSQL and maybe some other platforms.
但是它在 PostgreSQL 和其他一些平台上不起作用。
In PostgreSQL you can use DISTINCT ONclause:
在 PostgreSQL 中，您可以使用DISTINCT ON子句：
SELECT DISTINCT ON ("group") * FROM "mytable" ORDER BY "group", "age" DESC;
回答by sel
Using ranking method.
使用排名方法。
SELECT @rn := CASE WHEN @prev_grp <> groupa THEN 1 ELSE @rn+1 END AS rn,
@prev_grp :=groupa,
person,age,groupa
FROM users,(SELECT @rn := 0) r
HAVING rn=1
ORDER BY groupa,age DESC,person
回答by user130268
Not sure if MySQL has row_number function. If so you can use it to get the desired result. On SQL Server you can do something similar to:
不确定 MySQL 是否有 row_number 函数。如果是这样，您可以使用它来获得所需的结果。在 SQL Server 上，您可以执行以下操作：
CREATE TABLE p
(
person NVARCHAR(10),
gp INT,
age INT
);
GO
INSERT INTO p
VALUES ('Bob', 1, 32);
INSERT INTO p
VALUES ('Jill', 1, 34);
INSERT INTO p
VALUES ('Shawn', 1, 42);
INSERT INTO p
VALUES ('Jake', 2, 29);
INSERT INTO p
VALUES ('Paul', 2, 36);
INSERT INTO p
VALUES ('Laura', 2, 39);
GO
SELECT t.person, t.gp, t.age
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY gp ORDER BY age DESC) row
FROM p
) t
WHERE t.row = 1;
回答by Arthur C
axiac's solution is what worked best for me in the end. I had an additional complexity however: a calculated "max value", derived from two columns.
axiac 的解决方案最终对我最有效。然而，我有一个额外的复杂性：计算出的“最大值”，来自两列。
Let's use the same example: I would like the oldest person in each group. If there are people that are equally old, take the tallest person.
让我们使用相同的示例：我想要每个组中年龄最大的人。如果有年龄相同的人，则取最高的人。
I had to perform the left join two times to get this behavior:
我必须执行两次左连接才能获得此行为：
SELECT o1.* WHERE
(SELECT o.*
FROM `Persons` o
LEFT JOIN `Persons` b
ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL) o1
LEFT JOIN
(SELECT o.*
FROM `Persons` o
LEFT JOIN `Persons` b
ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL) o2
ON o1.Group = o2.Group AND o1.Height < o2.Height
WHERE o2.Height is NULL;
Hope this helps! I guess there should be better way to do this though...
希望这可以帮助！我想应该有更好的方法来做到这一点......
回答by Antonio Giovanazzi
My solution works only if you need retrieve only one column, however for my needs was the best solution found in terms of performance (it use only one single query!):
我的解决方案仅在您只需要检索一列时才有效，但是根据我的需要，在性能方面找到了最佳解决方案（它只使用一个查询！）：
SELECT SUBSTRING_INDEX(GROUP_CONCAT(column_x ORDER BY column_y),',',1) AS xyz,
column_z
FROM table_name
GROUP BY column_z;
It use GROUP_CONCAT in order to create an ordered concat list and then I substring to only the first one.
它使用 GROUP_CONCAT 来创建一个有序的 concat 列表，然后我只对第一个进行子串。
回答by Khalid Musa Sagar
I have a simple solution by using WHERE IN
我有一个简单的解决方案 WHERE IN
SELECT a.* FROM `mytable` AS a
WHERE a.age IN( SELECT MAX(b.age) AS age FROM `mytable` AS b GROUP BY b.group )
ORDER BY a.group ASC, a.person ASC
回答by kiruba
In Oracle below query can give the desired result.
在 Oracle 下面的查询中可以给出想要的结果。
SELECT group,person,Age,
ROWNUMBER() OVER (PARTITION BY group ORDER BY age desc ,person asc) as rankForEachGroup
FROM tablename where rankForEachGroup=1
