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

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

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

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中得到每组最多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值

其他回答

您可以连接一个取出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可以解释如下:

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过滤掉你需要的行

正确的解决方法是:

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('组中没有最大年龄')的满行null。 使用INNER JOIN会使这些行不匹配,它们会被忽略。

WHERE子句只保留从b中提取的字段中有null的行。它们是每个组中年龄最大的人。

进一步的阅读

这个解决方案和其他许多解决方案在《SQL反模式第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

如果需要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
    )