如何获得包含每个分组集的最大值的行?
我见过这个问题的一些过于复杂的变体,没有一个有好的答案。我试着把最简单的例子放在一起:
给一个这样的表,有个人、组和年龄列,你如何得到每组中年龄最大的人?(一组中平局的结果应按首字母顺序排列)
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 @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
这个sql可以解释如下:
Select * from users, (Select @rn:= 0) r
按组别、年龄、个人排序
@prev_grp为空
@rn:= CASE WHEN @prev_grp <> groupa THEN 1 ELSE @rn+1 END
这是一个三个运算符的表达式
就像这样,rn= 1 if prev_grp != groupa else rn=rn+1
让rn=1过滤掉你需要的行
在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,因此要使其生效,您必须没有此选项(编辑服务器的选项文件以删除此设置)。
使用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