mysql巧用连表查询各科成绩前三名

下列是各表的详情，不想自己建表的同学可以直接copy code，数据随意。 
创建表成绩详情表： 

CREATE TABLE score ( 
id int(10) NOT NULL AUTO_INCREMENT, 
subject_id int(10) DEFAULT NULL, 
student_id int(10) DEFAULT NULL, 
score float DEFAULT NULL, 
PRIMARY KEY (id) 
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8; 

 
创建学生表： 

CREATE TABLE student ( 
id int(10) NOT NULL AUTO_INCREMENT, 
name varchar(10) DEFAULT NULL, 
PRIMARY KEY (id) 
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; 

 
创建科目表： 

CREATE TABLE subject ( 
id int(10) NOT NULL AUTO_INCREMENT, 
name varchar(10) DEFAULT NULL, 
PRIMARY KEY (id) 
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; 

 

查询语句：

select a.id,a.subject_id,a.student_id,a.score from score as a left join score as b on a.subject_id=b.subject_id and a.score>=b.score
group by a.subject_id,a.student_id,a.score
having count(a.subject_id)>=4
order by a.subject_id,a.score desc;

分析：先将查询语句分别拆开来一步一步分析 

select a.id,a.subject_id,a.student_id,a.score,b.id,b.subject_id,b.student_id,b.score 
from score as a left join score as b on a.subject_id=b.subject_id; 

#这里把所有的列都列出来了便于对比 
这里把表score的每一条同subject_id的数据都连接起来，形成笛卡尔积，如图所示：共18*6=108条数据 

现在我们可以再进一步处理上面的数据了。这里我们再加上 a.score>=b.score 这个条件筛选再进行一次筛选。 

select a.id,a.subject_id,a.student_id,a.score,b.id,b.subject_id,b.student_id,b.score 
from score as a left join score as b on a.subject_id=b.subject_id and a.score>=b.score; 

a.score>=b.score 这里是在同一门课程中,将每一个分数与其他分数(包括自己)进行一一对比,只留下大于自己,或者等于自己的分数。

如果选择对比的行中的a.score是最高分,那么在后面利用group by a.subject_id,a.student_id,a.score分组的时候,此时计算得出的count(a.subject_id)就是最多的(count为总人数),因为其它的分数最多也只是和它一样多,其它的都比它低;同理,如果a.score是最低分,那么count(a.subject_id)是最少的(count最少为1,只有它自己,其余分数都比它高;最多为总人数,这种情况是其它人的分数都和最低分一样多...),其它的分数最差也和它一样多,其它的都比它要高。例如:

• 100分是最高的,所以几乎其他所有分数都符合100>=其他分数 这个条件,所以100分出现次数最多(count为总人数)
• 0分,是最低分,几乎其他所有分数都不符合0>=其他分数这个条件,所以0分出现的次数应该是最少的(count最少为1;最多为总人数,此时其他的分数也都是最低分,即大家分数一样低)

有同学可能会问为什么不用a.score > b.score来筛选。如果用a.score > b.score来进行筛选的话，如果数据中某个科目出现大量的并列第一名的话那么第一名就会被过滤掉，以至于得不到结果。如图： 

接下来就是分组：group by a.subject_id,a.student_id,a.score #按subject_id,student_id,score来进行分组

(这里使用group by a.subject_id,a.student_id,a.score和使用group by a.subject_id,a.student_id一样的,因为两表左连接之后,不可能出现相同的a.subject_id,a.student_id有多条不同的a.score的记录；因为同一个同学a.student_id,同一个科目a.subject_id,只能有一个分数a.score，一个同学不可能一个科目有多个不同的分数)； 

select a.id,a.subject_id,a.student_id,a.score,b.id,b.subject_id,b.student_id
b.score,count(a.subject_id) from score as a left join score as b 
on a.subject_id=b.subject_id and a.score>=b.score group by a.subject_id,a.student_id,a.score; 

添加count(a.subject_id)来进行对比易于理解 

分组后再进行条件查询：having count(a.subject_id)>=4；

下面来讨论下>=4是什么含义:正常来说,如果每门课程的各个同学的分数都不一样,那么同一门课程中从最高分到最低分的count(a.subject_id) 分别为:6，5，4，3，2，1；取count>=4就是取6，5，4即取count最多的三个，所以取出的数据就是排名前三(count从高到低,取前三,那么就是前三甲的记录)：
接下来就是排序：order by a.subject_id,a.score desc。

 

下面有篇文章,讲解的很详细:https://www.jianshu.com/p/fff5d1f71c0f

转载于:https://my.oschina.net/u/2331760/blog/3073066