如何获取包含每个分组最大值的行？
我看到了一些过于复杂的变体，但没有一个很好的答案。我试图提供最简单的例子：
给定下面这样的表格，具有人员、分组和年龄列，如何获取每个组中最年长的人？ （在一个组内的平局应该给出字母顺序排名靠前的结果）
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
正确的解决方案是：
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 的每一行与 b 中在列 Group 中具有相同值且在列 Age 中具有较大值的所有行匹配。在列 Age 中，如果来自 o 的任何行没有其组中最大值，则它将与 b 中的一行或多行匹配。
使用 LEFT JOIN 将组中最年长的人（包括那些独处在他们的组中的人）与 b 中的一个由 NULL 填充的行（“组中没有最大年龄”）匹配。
使用 INNER JOIN 会使这些行不匹配并被忽略。
WHERE 子句仅保留从 b 中提取的字段中具有 NULL 的行。它们是每个组中最年长的人。
该解决方案以及许多其他解决方案都在书籍《SQL反模式卷1：避免数据库编程的陷阱》中解释。
o.Age = b.Age，对于同一组可能会返回两行或更多行，例如，如果第二组的 Paul 和 Laura 一样都是39岁。但是，如果我们不想要这种情况，可以使用以下语句进行限制：ON o.Group = b.Group AND (o.Age < b.Age or (o.Age = b.Age and o.id < b.id))。 - Todor在mysql中有一种超级简单的方法来做到这一点：
select *
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`
sql-mode设置默认包括ONLY_FULL_GROUP_BY，因此为了使其正常工作，您必须不选择此选项（编辑服务器的选项文件以删除此设置）。SELECT子句中出现且未使用聚合函数计算的每个列或表达式的值（不一定来自同一行）。 - axiacSELECT子句中的列与GROUP BY列之间不存在函数依赖关系。如果它被配置为接受此查询(ONLY_FULL_GROUP_BY被禁用)，则会像以前的版本一样工作（即这些列的值是不确定的）。 - axiac你可以对子查询中检索的 MAX(Group) 和 Age 进行联接查询。这种方法在大多数关系型数据库管理系统上都是可移植的。
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;
Group = 2, Age = 20，子查询将返回其中一行，但是联结中的 ON 条件将匹配 _这两行_，因此会返回 2 行相同的组/年龄，尽管其他列的值不同，而不是一行。 - Michael BerkowskiSELECT DISTINCT ON ("group") * FROM "mytable" ORDER BY "group", "age" DESC;
不确定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;
改进axiac的解决方案以避免在每个组中选择多行，同时允许使用索引
SELECT o.*
FROM `Persons` o
LEFT JOIN `Persons` b
ON o.Group = b.Group AND o.Age < b.Age
LEFT JOIN `Persons` c
ON o.Group = c.Group AND o.Age = c.Age and o.id < c.id
WHERE b.Age is NULL and c.id is null
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
从 users 表和 (select @rn := 0) r 中选择所有列，并按照 groupa、age 和 person 的降序排序。
@prev_grp 为 null
@rn := CASE WHEN @prev_grp <> groupa THEN 1 ELSE @rn+1 END，这是一个三元运算表达式
类似于这样，如果 prev_grp != groupa，则 rn = 1，否则 rn = rn + 1
通过 having rn=1 过滤出所需的行
:= - 这是什么？ - Yarin我不会使用“Group”作为列名，因为它是保留字。然而，以下SQL语句可以正常工作。
SELECT a.Person, a.Group, a.Age FROM [TABLE_NAME] a
INNER JOIN
(
SELECT `Group`, MAX(Age) AS oldest FROM [TABLE_NAME]
GROUP BY `Group`
) b ON a.Group = b.Group AND a.Age = b.oldest
如果您只需要检索一列，我的解决方案才能起作用，然而对于我来说，在性能方面这是找到的最佳解决方案（它仅使用一个单一查询！）：
SELECT SUBSTRING_INDEX(GROUP_CONCAT(column_x ORDER BY column_y),',',1) AS xyz,
column_z
FROM table_name
GROUP BY column_z;
axiac的解决方案最终对我最有效。但是，我还有一个额外的复杂性：从两列中推导出一个计算得出的“最大值”。
让我们使用同样的例子：我想要每个组中最年长的人。如果有人一样老，就选身高最高的人。
我必须执行左连接两次才能实现这种行为：
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;