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<b.score来进行筛选的话，如果数据中某个科目出现大量的并列第一名的话那么第一名就会被过滤掉，以至于得不到结果。如图：

接下来就是分组：group by a.subject_id,a.student_id,a.score #按subject_id,student_id,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；
接下来就是排序：order by a.subject_id,a.score desc。