如何在mysql中查询每个分组的前几名

2024-03-17 04:02| 来源: 网络整理| 查看: 265

问题

在工作中常会遇到将数据分组排序的问题，如在考试成绩中，找出每个班级的前五名等。在orcale等数据库中可以使用partition 语句来解决，但在MySQL中就比较麻烦了。这次翻译的文章就是专门解决这个问题的

原文地址: How to select the first/least/max row per group in SQL

翻译

在使用SQL的过程中，我们经常遇到这样一类问题：如何找出每个程序最近的日志条目？如何找出每个用户的最高分？在每个分类中最受欢迎的商品是什么?通常这类“找出每个分组中最高分的条目”的问题可以使用相同的技术来解决。在这篇文章里我将介绍如何解决这类问题，而且会介绍如何找出最高的前几名而不仅仅是第一名。

这篇文章会用到行数(row number)，我在原来的文章 MySQL-specific 和 generic techniques 中已经提到过如何为每个分组设置行数了。在这里我会使用与原来的文章中相同的表格，但会加入新的price 字段

01 +--------+------------+-------+ 02 | type | variety | price | 03 +--------+------------+-------+ 04 | apple | gala | 2.79 | 05 | apple | fuji | 0.24 | 06 | apple | limbertwig | 2.87 | 07 | orange | valencia | 3.59 | 08 | orange | navel | 9.36 | 09 | pear | bradford | 6.05 | 10 | pear | bartlett | 2.14 | 11 | cherry | bing | 2.55 | 12 | cherry | chelan | 6.33 | 13 +--------+------------+-------+ 选择每个分组中的最高分

这里我们要说的是如何找出每个程序最新的日志记录或审核表中最近的更新或其他类似的排序问题。这类问题在IRC频道和邮件列表中出现的越来越频繁。我使用水果问题来作为示例，在示例中我们要选出每类水果中最便宜的一个，我们期望的结果如下

1 +--------+----------+-------+ 2 | type | variety | price | 3 +--------+----------+-------+ 4 | apple | fuji | 0.24 | 5 | orange | valencia | 3.59 | 6 | pear | bartlett | 2.14 | 7 | cherry | bing | 2.55 | 8 +--------+----------+-------+

这个问题有几种解法，但基本上就是这两步：找出最低的价格，然后找出和这个价格同一行的其他数据

其中一个常用的方法是使用自连接(self-join)，第一步根据type(apple, cherry etc)进行分组，并找出每组中price的最小值

01 select type, min(price) as minprice 02 from fruits 03 group by type; 04 +--------+----------+ 05 | type | minprice | 06 +--------+----------+ 07 | apple | 0.24 | 08 | cherry | 2.55 | 09 | orange | 3.59 | 10 | pear | 2.14 | 11 +--------+----------+

第二步是将刚刚结果与原来的表进行连接。既然刚刚给结果已经被分组了，我们将刚刚的查询语句作为子查询以便于连接没有被分组的原始表格。

01 select f.type, f.variety, f.price 02 from ( 03 select type, min(price) as minprice 04 from fruits group by type 05 ) as x inner join fruits as f on f.type = x.type and f.price = x.minprice; 06 07 +--------+----------+-------+ 08 | type | variety | price | 09 +--------+----------+-------+ 10 | apple | fuji | 0.24 | 11 | cherry | bing | 2.55 | 12 | orange | valencia | 3.59 | 13 | pear | bartlett | 2.14 | 14 +--------+----------+-------+

还可以使用相关子查询(correlated subquery)的方式来解决。这种方法在不同的mysql优化系统下，可能性能会有一点点下降，但这种方法会更直观一些。

01 select type, variety, price 02 from fruits 03 where price = (select min(price) from fruits as f where f.type = fruits.type); 04 +--------+----------+-------+ 05 | type | variety | price | 06 +--------+----------+-------+ 07 | apple | fuji | 0.24 | 08 | orange | valencia | 3.59 | 09 | pear | bartlett | 2.14 | 10 | cherry | bing | 2.55 | 11 +--------+----------+-------+

这两种查询在逻辑上是一样的，他们性能也基本相同

找出每组中前N个值

这个问题会稍微复杂一些。我们可以使用聚集函数(MIN(), MAX()等等)来找一行，但是找前几行不能直接使用这些函数，因为它们都只返回一个值。但这个问题还是可以解决的。

这次我们找出每个类型(type)中最便宜的前两种水果，首先我们尝试

01 select type, variety, price 02 from fruits 03 where price = (select min(price) from fruits as f where f.type = fruits.type) 04 or price = (select min(price) from fruits as f where f.type = fruits.type 05 and price > (select min(price) from fruits as f2 where f2.type = fruits.type)); 06 +--------+----------+-------+ 07 | type | variety | price | 08 +--------+----------+-------+ 09 | apple | gala | 2.79 | 10 | apple | fuji | 0.24 | 11 | orange | valencia | 3.59 | 12 | orange | navel | 9.36 | 13 | pear | bradford | 6.05 | 14 | pear | bartlett | 2.14 | 15 | cherry | bing | 2.55 | 16 | cherry | chelan | 6.33 | 17 +--------+----------+-------+

是的，我们可以写成自连接(self-join)的形式，但是仍不够好(我将这个练习留给读者)。这种方式在N变大(前三名，前4名)的时候性能会越来越差。我们可以使用其他的表现形式编写这个查询，但是它们都不够好，它们都相当的笨重和效率低下。(译者注：这种方式获取的结果时，如果第N个排名是重复的时候最后选择的结果会超过N，比如上面例子还有一个apple价格也是0.24，那最后的结果就会有3个apple)

我们有一种稍好的方式，在每个种类中选择不超过该种类第二便宜的水果

1 select type, variety, price 2 from fruits 3 where ( 4 select count(*) from fruits as f 5 where f.type = fruits.type and f.price

【本文地址】

公司简介

联系我们