获取每组分组结果的前n条记录 [英] Get top n records for each group of grouped results

问题描述

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

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

使用下面的表格，其中包含人员"，组"和年龄"列，您将如何让每个组中的2个最老的人呢?(组中的联系不应产生更多结果，而应给出前2个字母顺序)

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

所需结果集:

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

---

注意::该问题基于上一个问题-获取每组分组的最大值记录SQL结果-用于从每个组中获得一个第一行，并且从@Bohemian那里获得了MySQL的一个很好的答案:

---

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.

推荐答案

这是使用UNION ALL的一种方法(请参阅

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:

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

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;

请参见演示

这篇关于获取每组分组结果的前n条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！