如何获得包含每个分组集的最大值的行?
我见过这个问题的一些过于复杂的变体，没有一个有好的答案。我试着把最简单的例子放在一起:
给一个这样的表，有个人、组和年龄列，你如何得到每组中年龄最大的人?(一组中平局的结果应按首字母顺序排列)
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是否有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;
其他回答
我的解决方案只适用于你只需要检索一个列，但我的需求是在性能方面发现的最佳解决方案(它只使用一个查询!):
SELECT SUBSTRING_INDEX(GROUP_CONCAT(column_x ORDER BY column_y),',',1) AS xyz,
column_z
FROM table_name
GROUP BY column_z;
它使用GROUP_CONCAT以创建一个有序concat列表，然后我只将子字符串字符串到第一个。
不确定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;
这就是我如何在mysql中得到每组最多N行
SELECT co.id, co.person, co.country
FROM person co
WHERE (
SELECT COUNT(*)
FROM person ci
WHERE co.country = ci.country AND co.id < ci.id
) < 1
;
工作原理:
自连接到表 分组由co.country = ci.country完成 每组有N个元素由)< 1控制，因此对于3个元素-)< 3 获取Max或min取决于:co.id < ci.id Co.id < ci。Id - Max Co.id > ci。Id - min
完整的例子:
Mysql每组选择n个Max值
在mysql中有一个超级简单的方法:
select *
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`
这是可行的，因为在mysql中，你不允许聚合非group-by列，在这种情况下，mysql只返回第一行。解决方案是首先对数据进行排序，这样对于每个组，您想要的行是第一个，然后按照您想要的值的列进行分组。
避免了试图找到max()等复杂的子查询，也避免了当有多个具有相同最大值的行时返回多行的问题(因为其他答案会这样做)。
注意:这是一个仅适用于mysql的解决方案。我所知道的所有其他数据库都会抛出SQL语法错误，提示“未聚合的列未按子句列在组中”或类似的信息。因为这个解决方案使用了未记录的行为，所以更谨慎的人可能想要包含一个测试来断言，如果MySQL的未来版本改变了这个行为，它仍然可以工作。
5.7版本更新:
从5.7版开始，sql模式设置默认只包含了ONLY_FULL_GROUP_BY，因此要使其生效，您必须没有此选项(编辑服务器的选项文件以删除此设置)。
with CTE as
(select Person,
[Group], Age, RN= Row_Number()
over(partition by [Group]
order by Age desc)
from yourtable)`
`select Person, Age from CTE where RN = 1`
