mysql查询每门课程成绩最好的前两名学生id和姓名

创建库：create database db6;

库下面创建表：
班级表：
mysql> create table class(cid int not null unique auto_increment,
-> caption varchar(6) not null,
-> grade_id int not null);
学生表：
mysql> create table student(sid int not null unique auto_increment,
-> sname varchar(3) not null,
-> gender enum(‘male’,‘female’) not null default ‘male’,
-> class_id int not null);
老师表：
mysql> create table teacher(tid int not null unique auto_increment,
-> tname varchar(6));
课程表：
mysql> create table course(cid int not null unique auto_increment,
-> cname varchar(4) not null,
-> teacher_id int );
成绩表：
mysql> create table score(sid int not null unique auto_increment,
-> student_id int not null,
-> course_id int not null,
-> score int not null);
年级表：
mysql> create table class_grade(gid int not null unique auto_increment,
-> gname varchar(6) not null );
班级任职表
mysql> create table teach2cls(tcid int not null unique auto_increment,
-> tid int not null,
-> cid int not null);
自建数据：
班级表：
insert class (caption,grade_id) values
(‘一年一班’,1),(‘一年二班’,1),
(‘二年一班’,2),
(‘三年一班’,3),(‘三年二班’,3),(‘三年三班’,3),
(‘四年一班’,4),(‘四年二班’,4),(‘四年三班’,4),(‘四年四班’,4);
学生表：
insert student(sname,gender,class_id) values
(‘后羿’,‘male’,1),(‘盖伦’,‘male’,2),(‘潘金莲’,‘female’,1),(‘诸葛亮’,‘male’,3),
(‘曹操’,‘male’,3),(‘嬴政’,‘male’,4),(‘嫦娥’,‘female’,7),(‘黑老大’,‘female’,6),
(‘王老二’,‘male’,5);
老师表：
insert teacher(tname) values (‘狗蛋’),(‘二锅’),(‘老肥’);
课程表：
insert course(cname,teacher_id) values
(‘语文’,1),(‘数学’,2),(‘英语’,1),(‘物理’,3);
成绩表：
insert score(student_id,course_id,score) values
(1,1,61),(1,2,59),(1,3,10),
(2,4,80),(2,3,25),
(3,1,90),(3,2,33),(3,3,100),(3,4,16),
(4,1,100),(4,2,100),(4,4,100),
(5,1,95),(5,2,19),(5,3,59),
(6,3,11),(6,2,61),
(7,3,85),(7,4,99),
(8,1,85),
(9,2,100);
年级表：
insert class_grade(gname) values
(‘一年级’),(‘二年级’),(‘三年级’),(‘四年级’);
班级任职表：
insert teach2cls (tid,cid) values
(1,1),(1,3),(2,2),(3,4),(2,8),(3,6),(1,7),(3,10),(3,9),(1,5);

44、查询每门课程成绩最好的前两名学生id和姓名；

select * from score
where (select count(1)
from score as sc
where score.course_id = sc.course_id
and score.score < sc.score)<2
order by course_id,score desc;
#首先count(1)和count(*)都是统计记录的行数
然后，这个子查询的作用是记录当前查询行和子查询所有符合条件的数量
select count(1) from score as sc where score.course_id = sc.course_id and score.score < sc.score
比如：
id-------course-------score
1----------4 ---------100
2 ---------5 ------------90
3 --------6 ------------80
（只是举例子所列的表）
那么查询的效果就是 select * from score 按行查询
先匹配第一条记录 1-------------4---------------100
然后子查询 select count(1) from score as sc where 4 = sc.course_id and 100 <sc.score 结果0
然后匹配第二条记录 2----------5---------90
然后子查询 select count(1) from score as sc where 5=sc.course_id and 90< sc.score 结果为1
然后匹配第三条记录 3----------6----------80
然后子查询 select count(1) from score as sc where 6 = sc.course_id and 80 < sc.score 结果为2
那么：
又因后面比较了count的结果要<2,所以只有两条记录符合，
上面的原理大概这样：
首先有两个列表 s1 和 s2 ，
s1 是 select * from score as s1 获得 ， s2 是子查询中 select count(1) from score as s2 获得
此时s1的学生id、课程id、分数都是固定的
然后开始按照条件比较：
当s1 的课程id 为 4 的时候， s1.course_id = s2.course_id and s1.score < s2.score
------------------------>这里的s2.course_id 包含了表里所有学生只要课程id是4的所有记录
------------------------>然后就拿着s1 的分数 和 s2 所有学生的分数进行比较
------------------------->当s2 的学生分数大于s1 的这个学生分数的时候 count(1) 统计就会+1
------------------------>然后count<2，就只拿出两条记录