获取每组SQL结果的最大值记录

如何获得包含每个分组集的最大值的行?

我见过这个问题的一些过于复杂的变体，没有一个有好的答案。我试着把最简单的例子放在一起:

给一个这样的表，有个人、组和年龄列，你如何得到每组中年龄最大的人?(一组中平局的结果应按首字母顺序排列)

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
354513 次浏览
小开

你可以通过子查询来连接MAX(Group)和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;
小开

采用排名法。

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可以解释如下:

  1. select * from users， (select @rn:= 0 按组别、年龄描述、人物

    排序

  2. @prev_grp为空

  3. @rn:= CASE WHEN @prev_grp <>groupa THEN 1 ELSE @rn+1 END

    这是一个三运算符表达式
    这样，rn= 1 if prev_grp != groupa else rn=rn+1

  4. 有rn=1过滤出你需要的行

小开
最佳答案

在mysql中有一个超级简单的方法:

select *
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`

这是因为在mysql中，你允许不聚合非group-by列，在这种情况下，mysql只返回第一个行。解决方案是首先对数据进行排序，这样对于每个组，您想要的行是第一个，然后按照您想要的值的列进行分组。

你可以避免试图找到max()等复杂的子查询，也可以避免当有多个具有相同最大值的行时返回多行的问题(因为其他答案会这样做)。

这是一个mysql-only解决方案。我所知道的所有其他数据库都会抛出一个SQL语法错误，提示“未聚合的列未被子句列在组中”。或类似的。因为这个解决方案使用了无证行为，更谨慎的人可能想要包括一个测试，以断言它仍然是工作，如果MySQL的未来版本改变这一行为。

5.7版本更新:

从5.7版本开始，sql-mode设置默认包含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
小开 
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`
小开

你也可以试试

SELECT * FROM mytable WHERE age IN (SELECT MAX(age) FROM mytable GROUP BY `Group`) ;
小开

在PostgreSQL中，你可以使用不同的对子句:

SELECT DISTINCT ON ("group") * FROM "mytable" ORDER BY "group", "age" DESC;
小开

我不会使用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 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列中具有更大的值。o中的任何一行在Age列中没有其组的最大值，将匹配b中的一行或多行。

LEFT JOIN使它匹配组中最年长的人(包括组中单独的人)与来自b的充满__abc1的一行('组中没有最大年龄')。
使用INNER JOIN使这些行不匹配，它们将被忽略

WHERE子句只保留从b提取的字段中具有__abc1的行。他们是每个群体中年龄最大的人。

进一步的阅读

这个解决方案和许多其他的解释在书SQL反模式第1卷:避免数据库编程陷阱

小开

这种方法的好处是允许您根据不同的列进行排序，而不会破坏其他数据。如果您试图用一列物品列出订单，首先列出最重的，那么这种方法非常有用。

来源:http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

SELECT person, group,
GROUP_CONCAT(
DISTINCT age
ORDER BY age DESC SEPARATOR ', follow up: '
)
FROM sql_table
GROUP BY group;
小开

不确定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 O.*              -- > O for oldest table
from people O , people T
where O.grp = T.grp and
O.Age =
(select max(T.age) from people T where O.grp = T.grp
group by T.grp)
group by O.grp;
小开

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;

希望这能有所帮助!我想应该有更好的方法来做到这一点…

小开

我的解决方案只适用于你只需要检索一个列，但我的需求是在性能方面发现的最佳解决方案(它只使用一个查询!):

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列表，然后我只将子字符串字符串到第一个。

小开

如果需要mytable中的ID(以及所有的列)

SELECT
*
FROM
mytable
WHERE
id NOT IN (
SELECT
A.id
FROM
mytable AS A
JOIN mytable AS B ON A. GROUP = B. GROUP
AND A.age < B.age
)
小开

这就是我如何在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 & lt;ci。Id - Max
    • Co.id > ci。Id - min
      • 李< / ul > < / >

    完整的例子:

    mysql select n Max values per group

小开

在Oracle下面查询可以给出想要的结果。

SELECT group,person,Age,
ROWNUMBER() OVER (PARTITION BY group ORDER BY age desc ,person asc) as rankForEachGroup
FROM tablename where rankForEachGroup=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 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

提交答案