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
查询结果:
这种查询方式,某些逻辑无法理解。