以下是最简单的示例,尽管任何解决方案都应该能够扩展到需要许多 n 顶级结果:
给定下面这样的表格,其中包含人员、组和年龄列,您将如何获得每个组中年龄最大的 2 人? (组内的关系不应产生更多结果,但按字母顺序给出前 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 | +--------+-------+-----+
注意: 这个问题建立在前一个问题的基础上 - 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`
希望能够以此为基础,尽管我不知道如何。
这是使用 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
在其他数据库中,您可以使用 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
编辑我刚刚注意到 bluefeet 发布了一个非常相似的答案:+1 给他。然而,这个答案有两个小优点:
这是一个单一的查询。变量在 SELECT 语句中初始化。它处理问题中描述的关系(按名称的字母顺序)。
所以我会把它留在这里,以防它可以帮助某人。
JOIN (SELECT @prev := NULL, @rn := 0) AS vars
。我的想法是声明空变量,但对于 MySql 来说似乎无关紧要。
尝试这个:
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
a.person
的顺序。
如何使用自连接:
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
列。
编辑:
跟进 OP 关于缺少平局结果的评论,我增加了 snuffin 的回答以显示所有平局。这意味着如果最后一个是平局,则可以返回超过 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
ERROR 1242 (21000): Subquery returns more than 1 row
,大概是因为 GROUP BY
。当我单独执行 SELECT MIN
子查询时,它会生成三行:34, 39, 112
并且看起来第二个值应该是 36,而不是 39。
当您有大量行并且 Mark Byers/Rick James 和 Bluefeet 解决方案在我的环境(MySQL 5.6)上不起作用时,Snuffin 解决方案似乎执行起来很慢,因为 order by 在执行 select 之后应用,所以这里是一个变体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 秒的时间内返回结果
LIMIT 9999999
添加到具有 ORDER BY
的任何派生表。这可能防止 ORDER BY
被忽略。
\\:=
转义所有变量分配。
如果其他答案不够快,请尝试 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 | ...
看一下这个:
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
max(internal_version - 1)
- 所以压力更小:)
在 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
我想分享这个,因为我花了很长时间寻找一种简单的方法来在我正在处理的 java 程序中实现它。这并不能完全给出您正在寻找的输出,但它很接近。 mysql 中名为 GROUP_CONCAT()
的函数非常适合指定在每个组中返回多少个结果。使用 LIMIT
或尝试使用 COUNT
执行此操作的任何其他奇特方式对我来说都不起作用。因此,如果您愿意接受修改后的输出,这是一个很好的解决方案。假设我有一张名为“学生”的表,其中包含学生 ID、性别和 gpa。可以说我想为每个性别获得前 5 个 gpa。然后我可以这样写查询
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
变量并以不同的方式对其进行排序。所以如果我有学生的年龄,我可以用'age desc'替换'gpa desc',它会起作用!您还可以将变量添加到 group by 语句以在输出中获取更多列。所以这只是我发现的一种非常灵活的方法,如果你只列出结果就可以了。
在 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 此代码对每一行执行并将国家列的值存储到@current_country 变量。 @country_rank := IF(@current_country = country, @country_rank + 1, 1) 在这段代码中,如果 @current_country 相同,我们增加排名,否则将其设置为 1。对于第一行 @current_country 为 NULL,因此排名为也设置为 1。为了正确的排名,我们需要 ORDER BY country, population 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 部电影。
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
不定期副业成功案例分享
SELECT
中表达式的求值顺序(事实上,有时会乱序求值)。解决方案的关键是将所有变量赋值放在一个表达式中;这是一个示例:stackoverflow.com/questions/38535020/…。