mysql - 获取每组分组结果的前n条记录

Question

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

给定如下所示的表格，其中包含人物、组和年龄列，您将如何得到每组中最年长的 2 个人？(组内的关系不应产生更多结果，但给出前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`

希望能够以此为基础进行构建，尽管我不知道如何实现。

Answer 1

这是一种方法，使用 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