以下是最简单的示例,尽管任何解决方案都应能够扩展到需要很多n个顶级结果的地方:

给定下面的表格,其中包含“人员”,“组”和“年龄”列,您将如何获得每个组中年龄最大的2个人? (组内的关系不应产生更多结果,而应按字母顺序给出前两个)

+--------+-------+-----+
| Person | Group | Age |
+--------+-------+-----+
| Bob    | 1     | 32  |
| Jill   | 1     | 34  |
| Shawn  | 1     | 42  |
| Jake   | 2     | 29  |
| Paul   | 2     | 36  |
| Laura  | 2     | 39  |
+--------+-------+-----+

Desired result set:

+--------+-------+-----+
| Shawn  | 1     | 42  |
| Jill   | 1     | 34  |
| Laura  | 2     | 39  |
| Paul   | 2     | 36  |
+--------+-------+-----+

NOTE: This question builds on a previous one- Get records with max value for each group of grouped SQL results - for getting a single top row from each group, and which received a great MySQL-specific answer from @Bohemian:

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


我希望能够以此为基础,尽管我不知道如何做到。

最佳答案

这是使用UNION ALL的一种方法(请参见SQL Fiddle with Demo)。这适用于两个组,如果您有两个以上的组,则需要指定group编号并为每个group添加查询:

(
  select *
  from mytable
  where `group` = 1
  order by age desc
  LIMIT 2
)
UNION ALL
(
  select *
  from mytable
  where `group` = 2
  order by age desc
  LIMIT 2
)


有多种方法可以执行此操作,请参阅本文以确定适合您情况的最佳路线:

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

编辑:

这也可能对您有用,它会为每个记录生成一个行号。使用上面链接中的示例,这将仅返回行号小于或等于2的那些记录:

select person, `group`, age
from
(
   select person, `group`, age,
      (@num:=if(@group = `group`, @num +1, if(@group := `group`, 1, 1))) row_number
  from test t
  CROSS JOIN (select @num:=0, @group:=null) c
  order by `Group`, Age desc, person
) as x
where x.row_number <= 2;


Demo

关于mysql - 获取每组分组结果的前n条记录,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/56179544/

10-16 19:30