有时候会遇到这样的需求,那就是需要查询出前n名的信息。如果是不考虑并列的情况,那就简单了直接使用
排序加limit便可搞定。但是当考虑并列的情况是就不一样了。那么这个时候该怎么办呢?
不废话了,直接讲思路。我们可以把查询分为两部分。**一部分用于查询去除重复条件下的前n名成绩。第二部
分用户取出在这个区间的所有成员信息。**
来一个实战:
首先是成绩信息表,表名为te:
下面就是sql语句:
select aa.*
from
(
select DISTINCT name as n1,
(
select sum(mark)
from te
where name=n1
)as g
from te t
) aa
where aa.g in
(
select ta.grade as g
from
(
select
(select sum(mark)
from te
where name=t1.name
)as grade
from te t1
GROUP BY grade desc LIMIT 3
) as ta
)
看起来很复杂的样子,其实的确挺复杂的。下面来解释一下,首先我们要取出(这里是实现排名前三)在除去重复情
况下的前三名的成绩:
select (select sum(mark) from te where name=t1.name)as grade from te t1 GROUP BY
grade desc LIMIT 3
把每个人的总成绩计算出来,然后通过group by就可以去除重复,”desc“降序排序。limit 3取前三条。
这里使用了关键字limit,由于in后面的条件不能直接跟limit,所以将上面查询出来的结果放到一张ta表里,再进行一次查询。
(select ta.grade as g from (select (select sum(mark) from te where name=t1.name)
as grade from te t1 GROUP BY grade desc LIMIT 3) as ta)
好的,到这里就把去除重复条件下的前三名成绩取出来了。下面就该取学生信息了。通过将总成绩算出来之后,用in
关键字去匹配上面的结果集便可。
那么问题又来了,**我们查出的总成绩是通过计算得出的,并不是表的字段。所以这里要用到别名来保存。但是呢,
我们又要用这个别名去做匹配。好的,我们都知道where后面是不能直接跟上别名的。解决方法跟上面是一样的,就
是先把查出的结果集放到一张表里,然后再进行一次查询便可**
select aa.* from (select DISTINCT name as n1,(select sum(mark) from te where name=n1)
as g from te t) aa where aa.g in (结果集)
查询结果截图:
好的,以上就是我个人的一个解决方法了。那么也希望大家有什么更好的解决方法也能分享给我。
最后方便大家测试,将建表的sql语句以及插入数据的sql语句附在后面:
create table te (
name char(20) ,
lesson char(20),
mark float
)
insert into te values('john','Math',60);
insert into te values('john','Eng',50);
insert into te values('john','HIstory',56);
insert into te values('Mike','Eng',51);
insert into te values('Mike','Math',59);
insert into te values('Mike','HIstory',55);
insert into te values('Mark','Eng',71);
insert into te values('Mark','Math',89);
insert into te values('Mark','HIstory',95);
insert into te values('mm','Eng',61);
insert into te values('mm','Math',79);
insert into te values('mm','HIstory',85);
insert into te values('f','Eng',51);
insert into te values('f','Math',69);
insert into te values('f','HIstory',95);