mysql特殊查询----分组后排序 - 抄手砚

时间:2024-03-02 12:54:09

mysql特殊查询----分组后排序

使用的示例表

学生表----student

表结构

数据

 

 

 

 

 

 

 

 

查询方法

一、第一种方法

  我认为这是比较传统,比较容易理解的一种方式,使用自连接,并在连接条件中作比较,之后再对查询条件分组统计,排序。

  

select a.id,a.class,a.source
from student a left join student b on a.class=b.class and a.source<=b.source
group by a.class,a.source
order by a.class,a.source

结果:

分析一下查询过程:

1、自连接并使用比较条件

  

select a.id,a.class,a.source asource,b.source bsource
from student a left join student b on a.class=b.class and a.source<=b.source
order by a.class,a.source

查询结果:

 

以上查询数据可以看出,每个班等于或比每一个asource分数多bsource有几条数据,最终,最小的asource会有一个班级人数的数据条数,最大的asource只会有一条数据。这就是排序的依据。

之后,对数据分组。

2、对数据进行分组

select a.id,a.class,a.source asource,count(a.source) 
from student a left join student b on a.class=b.class and a.source<=b.source
group by a.class,a.source
order by a.class,a.source

查询结果:

以上数据已经可以直观的看出数据被排序后的结果。

3、对分组后的数据截取前N条或后N条

 a、首先是保留前N条数据,使用having。

select a.id,a.class,a.source asource,count(a.source) 
from student a left join student b on a.class=b.class and a.source<=b.source
group by a.class,a.source
having count(a.source)<=3
order by a.class,a.source

查询结果:

这样截取到的每个班最高的三个分数,因为比较条件是:a.source<=b.source,导致asource最大的分数只有一条,最小的有最多的条数,所以在使用having获得统计数最少的三条数据时,会得到三个最高分。

如果要得到最低的三个分数的数据,就要保证最小的分数有最少的数据(不一定是一条,当有两个最高分)。因为a.source<=b.source比较条件是相互的,所以只需要将asource参与的查询换成bsource,就可以实现获得最少分数的需求。

b、获得后N条数据

select a.id,a.class,b.source bsource,count(b.source) 
from student a left join student b on a.class=b.class and a.source<=b.source
group by a.class,b.source
having count(b.source)<=3
order by a.class,b.source

查询结果:

二、第二种方法-----只用于分组排序后取前N条数据

  1、分组后取前N或后N条,关键在于比较,通过where俩控制查询条数

  最大的前3条数据

select * from student as a 
where 3>(select count(*) from student where class=a.class and source>a.source) 
ORDER BY class ,source desc

查询结果:

最小的前3条数据

select * from student as a 
where 3>(select count(*) from student where class=a.class and source<a.source) 
ORDER BY class ,source

查询结果:

这种查询方式,某些逻辑无法理解。