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

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

给定如下表，其中包含人、组和年龄列，您将如何获得每个组中最年长的 2 个人?(组内的关系不应产生更多结果，而是给出按字母顺序排列的前 2 个)

<前>+--------+-------+-----+|人 |集团 |年龄 |+--------+-------+-----+|鲍勃 |1 |32 ||吉尔 |1 |34 ||肖恩 |1 |42 ||杰克 |2 |29 ||保罗 |2 |36 ||劳拉 |2 |39 |+--------+-------+-----+

期望的结果集:

<前>+--------+-------+-----+|肖恩 |1 |42 ||吉尔 |1 |34 ||劳拉 |2 |39 ||保罗 |2 |36 |+--------+-------+-----+

<小时>

注意:这个问题建立在上一个问题的基础上 - 获取每组分组的最大值的记录SQL 结果 - 用于从每个组中获得一个顶行，并且从 @Bohemian 那里收到了一个很好的 MySQL 特定答案:

选择 *from (select * from mytable order by `Group`, Age desc, Person) x按组"分组

希望能够以此为基础，尽管我不知道如何.

解决方案

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

<代码>(选择 *来自 mytable其中`组`= 1按年龄排序限制 2)联合所有(选择 *来自 mytable其中`组`= 2按年龄排序限制 2)

有多种方法可以做到这一点，请参阅本文以确定适合您情况的最佳路线:

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

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

选择人物，`group`，年龄从(选择人、组"、年龄、(@num:=if(@group = `group`, @num +1, if(@group := `group`, 1, 1))) row_number从测试 t交叉连接(选择@num:=0，@group:=null)c按组"、年龄、人排序) 作为 x其中 x.row_number <= 2;

参见演示

The following is the simplest possible example, though any solution should be able to scale to however many n top results are needed:

Given a table like that below, with person, group, and age columns, how would you get the 2 oldest people in each group? (Ties within groups should not yield more results, but give the first 2 in alphabetical order)

+--------+-------+-----+
| 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`

Would love to be able to build off this, though I don't see how.

解决方案

Here is one way to do this, using UNION ALL (See SQL Fiddle with Demo). This works with two groups, if you have more than two groups, then you would need to specify the group number and add queries for each 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
)

There are a variety of ways to do this, see this article to determine the best route for your situation:

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

Edit:

This might work for you too, it generates a row number for each record. Using an example from the link above this will return only those records with a row number of less than or equal to 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;

See Demo