获取每组分组结果的前n条记录-Java 学习之路

118

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

给出如下表格，包含人，组和年龄列，你将如何 get the 2 oldest people in each group? （组内的关系不应该产生更多结果，但按字母顺序给出前2个）

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

NOTE: 这个问题 Build 在前一个Get records with max value for each group of grouped SQL results的基础上 - 从每个组中获得一个顶行，并且从@Bohemian获得了一个特定的MySQL特定答案：

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

我希望能够 Build 起来，但我不知道如何 .

10 回答

30
在其他数据库中，您可以使用 ROW_NUMBER 执行此操作 . MySQL不支持 ROW_NUMBER ，但您可以使用变量来模拟它：
```
SELECT
    person,
    groupname,
    age
FROM
(
    SELECT
        person,
        groupname,
        age,
        @rn := IF(@prev = groupname, @rn + 1, 1) AS rn,
        @prev := groupname
    FROM mytable
    JOIN (SELECT @prev := NULL, @rn := 0) AS vars
    ORDER BY groupname, age DESC, person
) AS T1
WHERE rn <= 2
```
看到它在线工作：sqlfiddle

Edit 我刚注意到bluefeet发布了一个非常相似的答案：1给他 . 然而，这个答案有两个小优点：
- 这是一个单一的查询 . 变量在SELECT语句中初始化 .
- 它处理问题中描述的关系（按名称的字母顺序） .
所以我会留在这里，以防它可以帮助别人 .
回复于 2024-05-04T02:38:56+08:00

如果其他答案不够快，请试试this code：

SELECT
        province, n, city, population
    FROM
      ( SELECT  @prev := '', @n := 0 ) init
    JOIN
      ( SELECT  @n := if(province != @prev, 1, @n + 1) AS n,
                @prev := province,
                province, city, population
            FROM  Canada
            ORDER BY
                province   ASC,
                population DESC
      ) x
    WHERE  n <= 3
    ORDER BY  province, n;

输出：

+---------------------------+------+------------------+------------+
| province                  | n    | city             | population |
+---------------------------+------+------------------+------------+
| Alberta                   |    1 | Calgary          |     968475 |
| Alberta                   |    2 | Edmonton         |     822319 |
| Alberta                   |    3 | Red Deer         |      73595 |
| British Columbia          |    1 | Vancouver        |    1837970 |
| British Columbia          |    2 | Victoria         |     289625 |
| British Columbia          |    3 | Abbotsford       |     151685 |
| Manitoba                  |    1 | ...

回复于 2024-05-04T02:38:56+08:00

5
当你有大量行并且Mark Byers / Rick James和Bluefeet解决方案不适用于我的环境（MySQL 5.6）时，Snuffin解决方案似乎执行起来很慢，因为在执行select之后会应用order by，所以这里有一个变体Marc Byers / Rick James解决方案来解决这个问题（带有额外的叠加选择）：
```
select person, groupname, age
from
(
    select person, groupname, age,
    (@rn:=if(@prev = groupname, @rn +1, 1)) as rownumb,
    @prev:= groupname 
    from 
    (
        select person, groupname, age
        from persons 
        order by groupname ,  age desc, person
    )   as sortedlist
    JOIN (select @prev:=NULL, @rn :=0) as vars
) as groupedlist 
where rownumb<=2
order by groupname ,  age desc, person;
```
我在一个有5百万行的表上尝试了类似的查询，它在不到3秒的时间内返回结果
回复于 2024-05-04T02:38:56+08:00

试试这个：

SELECT a.person, a.group, a.age FROM person AS a WHERE 
(SELECT COUNT(*) FROM person AS b 
WHERE b.group = a.group AND b.age >= a.age) <= 2 
ORDER BY a.group ASC, a.age DESC

DEMO

回复于 2024-05-04T02:38:56+08:00

看一下这个：

SELECT
  p.Person,
  p.`Group`,
  p.Age
FROM
  people p
  INNER JOIN
  (
    SELECT MAX(Age) AS Age, `Group` FROM people GROUP BY `Group`
    UNION
    SELECT MAX(p3.Age) AS Age, p3.`Group` FROM people p3 INNER JOIN (SELECT MAX(Age) AS Age, `Group` FROM people GROUP BY `Group`) p4 ON p3.Age < p4.Age AND p3.`Group` = p4.`Group` GROUP BY `Group`
  ) p2 ON p.Age = p2.Age AND p.`Group` = p2.`Group`
ORDER BY
  `Group`,
  Age DESC,
  Person;

SQL小提琴：http://sqlfiddle.com/#!2/cdbb6/15

回复于 2024-05-04T02:38:56+08:00

在SQL Server中 row_numer() 是一个功能强大的函数，可以轻松获得结果，如下所示

select Person,[group],age
from
(
select * ,row_number() over(partition by [group] order by age desc) rn
from mytable
) t
where rn <= 2

回复于 2024-05-04T02:38:56+08:00

6
这是一种方法，使用 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
回复于 2024-05-04T02:38:56+08:00

-1

如何使用自加入：

CREATE TABLE mytable (person, groupname, age);
INSERT INTO mytable VALUES('Bob',1,32);
INSERT INTO mytable VALUES('Jill',1,34);
INSERT INTO mytable VALUES('Shawn',1,42);
INSERT INTO mytable VALUES('Jake',2,29);
INSERT INTO mytable VALUES('Paul',2,36);
INSERT INTO mytable VALUES('Laura',2,39);

SELECT a.* FROM mytable AS a
  LEFT JOIN mytable AS a2 
    ON a.groupname = a2.groupname AND a.age <= a2.age
GROUP BY a.person
HAVING COUNT(*) <= 2
ORDER BY a.groupname, a.age DESC;

给我：

a.person    a.groupname  a.age     
----------  -----------  ----------
Shawn       1            42        
Jill        1            34        
Laura       2            39        
Paul        2            36

Bill Karwin的回答给了我很大的启发Select top 10 records for each category

另外，我正在使用SQLite，但这应该适用于MySQL .

另一件事：在上面，为方便起见，我用 groupname 列替换了 group 列 .

Edit ：

关于OP关于缺失领带结果的评论的后续跟进，我在snuffin的回答中增加了显示所有关系 . 这意味着如果最后一个是tie，则可以返回超过2行，如下所示：

.headers on
.mode column

CREATE TABLE foo (person, groupname, age);
INSERT INTO foo VALUES('Paul',2,36);
INSERT INTO foo VALUES('Laura',2,39);
INSERT INTO foo VALUES('Joe',2,36);
INSERT INTO foo VALUES('Bob',1,32);
INSERT INTO foo VALUES('Jill',1,34);
INSERT INTO foo VALUES('Shawn',1,42);
INSERT INTO foo VALUES('Jake',2,29);
INSERT INTO foo VALUES('James',2,15);
INSERT INTO foo VALUES('Fred',1,12);
INSERT INTO foo VALUES('Chuck',3,112);


SELECT a.person, a.groupname, a.age 
FROM foo AS a 
WHERE a.age >= (SELECT MIN(b.age)
                FROM foo AS b 
                WHERE (SELECT COUNT(*)
                       FROM foo AS c
                       WHERE c.groupname = b.groupname AND c.age >= b.age) <= 2
                GROUP BY b.groupname)
ORDER BY a.groupname ASC, a.age DESC;

给我：

person      groupname   age       
----------  ----------  ----------
Shawn       1           42        
Jill        1           34        
Laura       2           39        
Paul        2           36        
Joe         2           36        
Chuck       3           112

回复于 2024-05-04T02:38:56+08:00

51
在MySQL - How To Get Top N Rows per Each Group有一个非常好的答案来解决这个问题

根据引用链接中的解决方案，您的查询将如下所示：
```
SELECT Person, Group, Age
   FROM
     (SELECT Person, Group, Age, 
                  @group_rank := IF(@group = Group, @group_rank + 1, 1) AS group_rank,
                  @current_group := Group 
       FROM `your_table`
       ORDER BY Group, Age DESC
     ) ranked
   WHERE group_rank <= `n`
   ORDER BY Group, Age DESC;
```
其中 n 是 top n ， your_table 是表的名称 .

我认为参考文献中的解释非常明确 . 为了快速参考，我将在此处复制并粘贴它：

目前MySQL不支持可以在组内分配序列号的ROW_NUMBER（）函数，但作为一种解决方法，我们可以使用MySQL会话变量 . 这些变量不需要声明，可以在查询中用于执行计算和存储中间结果 . @current_country：= country此代码针对每一行执行，并将country列的值存储到@current_country变量 . @country_rank：= IF（@ current_country = country，@ country_rank 1,1）在这段代码中，如果@current_country是相同的，我们增加rank，否则将其设置为1.对于第一行@current_country为NULL，所以rank也是设置为1.为了正确排名，我们需要有ORDER BY国家，人口DESC
回复于 2024-05-04T02:38:56+08:00
2
我想分享这个，因为我花了很长时间在java程序中寻找一种简单的方法来实现这一点我很好地提供了你正在寻找的输出但它的结果 . mysql中的函数 GROUP_CONCAT() 非常适合指定每组返回多少结果 . 使用 LIMIT 或任何其他尝试使用 COUNT 并且愿意接受修改输出的花哨方式，这是一个很好的解决方案 . 假设我有一张名为'student'的表，其中包含学生ID，性别和gpa . 让我们说我想为每个性别排名前5加帕斯 . 然后我可以像这样编写查询
```
SELECT sex, SUBSTRING_INDEX(GROUP_CONCAT(cast(gpa AS char ) ORDER BY gpa desc), ',',5) 
AS subcategories FROM student GROUP BY sex;
```
请注意，参数“5”表示要连接到每行的条目数

输出看起来像
```
+--------+----------------+
| Male   | 4,4,4,4,3.9    |
| Female | 4,4,3.9,3.9,3.8|
+--------+----------------+
```
您也可以更改 ORDER BY 变量并以不同的方式对它们进行排序 . 所以，如果我有学生's age I could replace the ' gpa desc ' with ' age desc'，那就行了！您还可以将变量添加到group by语句以在输出中获取更多列 . 所以这只是我发现的一种非常灵活的方式，如果你只是列出结果就可以了 .
回复于 2024-05-04T02:38:56+08:00

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

10 回答

相关问题