在面试的时候我经常会给毕业生出这样一道SQL题:
有一张表studentgrade,包含三个字段name,subject和grade,如下所示:
name subject grade
a 数学 99
b 英语 67
a 英语 77
题目要求应试者写出sql,查询每个人的最高分和最高分的科目,比如
a 数学 99
下面分析这个SQL的查询方法,分组函数是最常使用的,下面的分组函数可以得到最高分,姓名
select name,max(grade) from studentgrade group by name;
这样显然得不到具体的科目,要得到科目怎么办呢?使用临时表就是一个方案:
select b.name,b.grade,b.subject from
(select name, max(grade) grade from studentgrade group by name ) a,
studentgrade b
where a.name = b.name and a.grade = b.grade;
如果是需要查询每个人前三个最高分的成绩和科目呢?分组函数ms就无能为力了。下面是是几个不错的方式:
1、对每条记录进行检验,查询这条记录是不是排在前三位的
select a.* from studentgrade a where 3 > (select count(*) from studentgrade where name=a.name and grade< a.grade) order by a.name,a.grade;
为提高查询效率,在name和grade上面建立联合索引。
2、先把每个同学最大的三个科目分数查询出来形成一个集合,然后对每条记录进行检验,查看是否在这个集合里面。这种方式是mysql特有的,使用了top关键字。
select a.* from studentgrade a where grade in (select top 3 grade from studentgrade where
name=a.name order by grade) order by a.name,a.grade;
3、和第一种方式差不多,但使用的exists关键字。
select a.* from studentgrade a where exists (select count(*) from studentgrade where name=a.name and grade<a.grade having Count(*) < 2) order by a.name;