昨天在群里看到群里有人在讨论一个分组查询的问题,这个问题的大概意思是:在一张表里有班级、成绩、学生姓名、id,现在要求获取每个班级里成绩前2的学生信息。>初看,这个问题其实很简单,把每个班级的成绩按降序排序,然后取出排序前2的学生就可以了。
确实,如果单从思维层来说确实可以这么做,但现在这些数据是在数据表中,现在的要求是通过 mysql 查询语句来实现,要怎么做呢?
在这张表中有好几个班级,所以你有可能首先想到的是group by分组,但group by分组后取出来的数据只有一条,因此肯定不能用group by。
我们不妨换一种思路,我们可不可以对比成绩,拿班级里一个学生的成绩和班级里其他学生成绩对比,然后统计出分数比他高的人数,最后只需通过这个统计的人数来获取班级前2的学生信息记录。
下面用实例来具体说明:
数据表信息:(表明:grade)
根据上面的逻辑整理后的信息表:
通过上面两张表可以看出,如果想要获取每个班级前2的学习记录,只需查询成绩比他高的人数小于2的学生就可以了。
具体的 mysql 查询语句:
select * from grade g1 where ( select count(*) from grade g2 where g2.class=g1.class and g2.score>g1.score )<2 order by g1.class asc,g1.score desc
这句查询语句的意思是:查询整个表的数据,在查询过程中统计出班里成绩比他高的人数,然后筛选出这个统计人数小于2的学生记录。其实,不要后面的 order by 也能查出我们想要的结果,只不过结果比较混乱。而加上 order by排序是让查询结果以班级排序,然后每个班级里又按成绩降序排序,让查询结果一目了然。
通过上面的获取每个班级前 2 的学生记录,我们同样可以获取排名倒数 2 名的学生记录:
select * from grade g1 where( select count(*) from grade g2 where g2.class=g1.class and g2.score<g1.score )<2 order by g1.class asc, g1.score asc
这个成绩查询只不过是一个引子,还有很多其它场景的应用与此类似,比如查询每个部门工资最高的几人,商品中每类商品中价格最高的几样产品等等,只要摸透了这个每组查询筛选信息的逻辑,其它的就不成问题。