如何获得包含每个分组集的最大值的行?
我见过这个问题的一些过于复杂的变体，没有一个有好的答案。我试着把最简单的例子放在一起:
给一个这样的表，有个人、组和年龄列，你如何得到每组中年龄最大的人?(一组中平局的结果应按首字母顺序排列)
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
LEFT JOIN `Persons` b
ON o.Group = b.Group AND o.Age < b.Age
WHERE b.Age is NULL
group by o.Group
其他回答
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`
不确定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;
使用CTEs -常用表表达式:
WITH MyCTE(MaxPKID, SomeColumn1)
AS(
SELECT MAX(a.MyTablePKID) AS MaxPKID, a.SomeColumn1
FROM MyTable1 a
GROUP BY a.SomeColumn1
)
SELECT b.MyTablePKID, b.SomeColumn1, b.SomeColumn2 MAX(b.NumEstado)
FROM MyTable1 b
INNER JOIN MyCTE c ON c.MaxPKID = b.MyTablePKID
GROUP BY b.MyTablePKID, b.SomeColumn1, b.SomeColumn2
--Note: MyTablePKID is the PrimaryKey of MyTable
在PostgreSQL中，你可以使用DISTINCT ON子句:
SELECT DISTINCT ON ("group") * FROM "mytable" ORDER BY "group", "age" DESC;
你也可以试试
SELECT * FROM mytable WHERE age IN (SELECT MAX(age) FROM mytable GROUP BY `Group`) ;
