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

查询结果：

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