如何用SQL实现组内前几名的输出

时间:2024-09-22 11:34:02

关于问题

如何查询组内最大的,最小的,大家或许都知道,无非是min、max的函数使用。可是如何在MySQL中查找组内最好的前两个,或者前三个?

什么是相关子查询

在提出对于这个问题的对应方法之前,首先来理解一个概念:相关子查询。

所谓相关子查询,就是其查询的执行依赖于外部查询。多数情况下是子查询的where子句中引用了外部查询的表。执行过程:
  • 从外层查询中取出一个元组,将元组相关列的值传给内层查询
  • 执行内层查询,得到子查询操作的值
  • 外查询根据子查询返回的结果或结果集得到满足条件的行
  • 然后外层查询取出下一个元组重复做步骤1-3,直到外层的元组全部处理完毕。

下面会结合例子,在说明组内取值的同时,是如何使用相关子查询的。

在MySQL中查询每组中的前N个值

如何用SQL实现组内前几名的输出
有如上的表,我们需要找出每个类型(type)中最便宜的前两种水果,我们可以采取这样的方法:
select type, variety, price
from fruits
where (
select count(*) from fruits as f
where f.type = fruits.type and f.price < fruits.price
) <= 1;
6
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 < fruits.price
6
) <= 1;

这个查询语句就使用到了相关子查询,结合之前我们提到的相关子查询的查询步骤,我们来解析一下这个SQL语句。

从表中首先取出第一条数据,type为apple,variety为gala,price为2.79。相关子查询中,会将元组相关列的值传给内层查询,在子查询中我们看到
select count(*) from fruits as f
where f.type = fruits.type and f.price < fruits.price
2
1
select count(*) from fruits as f
2
where f.type = fruits.type and f.price < fruits.price

也就是说,实际上这条语句此时应该是这样
select count(*) from fruits as f
where f.type = apple and f.price < 2.79
2
1
select count(*) from fruits as f
2
where f.type = apple and f.price < 2.79
即,在apple这个类别中,价格比2.79还要低的元组的数量。

我们再看全貌
select type, variety, price
from fruits
where (
select count(*) from fruits as f
where f.type = apple and f.price < 2.79
) <= 1;
6
1
select type, variety, price
2
from fruits
3
where (
4
   select count(*) from fruits as f
5
   where f.type = apple and f.price < 2.79
6
) <= 1;
可以知道,apple中价格低于2.79的只有fuji这个元组,即值为1,满足子条件<=1的条件,所以该元组会被输出。

如果不好理解,这样来看。其实这个子条件的意思就是在说,我提取一个元组A,如果这个元组A所属的类别中,价格比元组A低的最多只有一个,那么就输出(即它本身是最便宜的,那么比它价格还低的就只有0个;或者它是第二便宜的,那么价格比它低的就只有1个。这样一来,只要数量条件为<=1,那么就得到了最便宜的两种水果)

按照相关子查询的执行顺序,第一个元组符合条件,被输出。接下来,把第二个元组的值带入,如果符合条件则输出,接着第三个元组第四个元组... 直到所有的元组带入筛选完毕,整个查询过程也就结束了。

什么,你说价格排列没有顺序?加上order就可以了。
select type, variety, price
from fruits
where (
select count(*) from fruits as f
where f.type = fruits.type and f.price < fruits.price
) <= 1
order by fruits.type, fruits.price
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 < fruits.price
6
) <= 1
7
order by fruits.type, fruits.price
如何用SQL实现组内前几名的输出 

参考链接