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

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

如果给出一个类似下面这样的表格，其中包含人员、组和年龄列，那么你会如何选择 每组中最年长的两个人？(组内的关系不应该产生更多的结果，而应该给出前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  |
+--------+-------+-----+

注意: 这个问题建立在前一个问题的基础上—— 获取每组 SQL 结果的最大值记录——从每个组中获得一个单独的最上一行，它从@Bohemian 得到了一个很棒的 MySQL 特有的答案:

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

我很希望能在这个基础上再建一个，虽然我不知道该怎么做。

219077 次浏览
小开
最佳答案

这里有一种方法可以做到这一点，使用 UNION ALL(参见 演示中的 SQL 小提琴)。这适用于两个组，如果您有两个以上的组，那么您需要指定 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;

参见 演示

小开

如何使用自我连接:

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

比尔 · 卡尔文对 为每个类别选择前10条记录的回答给了我很大的启发

此外，我正在使用 SQLite，但这应该可以在 MySQL 上工作。

另一件事: 在上面的代码中，为了方便起见，我将 group列替换为 groupname列。

编辑 :

跟进 OP 关于缺失领带结果的评论，我增加了鼻烟的回答，以显示所有的领带。这意味着如果最后一行是 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
小开

试试这个:

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

演示

小开

看看这个:

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 Fiddle: http://sqlfiddle.com/#!2/cdbb6/15

小开

在其他数据库中，可以使用 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

编辑 我刚刚注意到蓝脚怪发布了一个非常相似的答案: + 1给他。然而，这个答案有两个小优势:

  1. 这是一个单独的查询。变量在 SELECT 语句中初始化。
  2. 它处理问题中描述的关系(按名称字母顺序)。

所以我把它放在这里，以防它能帮到什么人。

小开

如果其他的答案不够快，试试 这个密码:

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 | ...
小开

在 SQLServerrow_numer()是一个功能强大的函数，可以很容易地得到如下结果

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

当你有很多行的时候，Snuffin 解决方案执行起来似乎很慢，Mark Byers/Rick James 和 Bluefeet 解决方案在我的环境(MySQL 5.6)中不起作用，因为在执行 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;

我在一个有500万行的表上尝试了类似的查询，它在不到3秒内返回 result

小开

我想分享这个，因为我花了很长时间寻找一个简单的方法来实现这个 Java 程序，我正在工作。这并不能完全给出您想要的输出，但是已经很接近了。Mysql 中名为 GROUP_CONCAT()的函数在指定每个组中返回多少结果方面工作得非常好。使用 LIMIT或任何其他花哨的方式尝试这样做与 COUNT不适合我。因此，如果你愿意接受一个修改输出，这是一个伟大的解决方案。假设我有一个名为“学生”的表，其中包含学生 ID、性别和绩点。比方说，我希望每个性别的平均成绩都能达到前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变量并以不同的方式对它们进行排序。因此，如果我有学生的年龄，我可以取代’平均绩点’与’年龄的 Desc’，它将工作！还可以通过语句将变量添加到组中，以便在输出中获取更多列。所以这只是一个方法，我发现这是相当灵活，工程良好，如果你只是列出结果。

小开

对于这个问题，MySQL-如何获得每组顶部 N 行有一个非常好的答案

根据引用链接中的解决方案，您的查询将类似于:

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 与 we 增量 rank 相同, 否则将其设置为1。对于第一行@current _ country 是 NULL，因此 Rank 也被设置为1。

为了获得正确的排名，我们需要按国家、人口排序

小开 
SELECT
p1.Person,
p1.`GROUP`,
p1.Age
FROM
person AS p1
WHERE
(
SELECT
COUNT( DISTINCT ( p2.age ) )
FROM
person AS p2
WHERE
p2.`GROUP` = p1.`GROUP`
AND p2.Age >= p1.Age
) < 2
ORDER BY
p1.`GROUP` ASC,
p1.age DESC

参考密码参考密码

小开 
WITH cte_window AS (
SELECT movie_name,director_id,release_date,
ROW_NUMBER() OVER( PARTITION BY director_id ORDER BY release_date DESC) r
FROM movies
)
SELECT * FROM cte_window WHERE r <= <n>;

以上查询将返回每个导演的最新 n 部电影。


提交答案