mysql實現(xiàn)查詢每門課程成績最好的前兩名學生id和姓名

更新時間：2023年11月28日 09:36:09 作者：小蟲子啊

這篇文章主要介紹了mysql實現(xiàn)查詢每門課程成績最好的前兩名學生id和姓名方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

創(chuàng)建庫

create database db6;

庫下面創(chuàng)建表

班級表：

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);

自建數(shù)據(jù)

班級表：

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),(‘數(shù)學',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);

查詢每門課程成績最好的前兩名學生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(*)都是統(tǒng)計記錄的行數(shù)

然后，這個子查詢的作用是記錄當前查詢行和子查詢所有符合條件的數(shù)量

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、分數(shù)都是固定的

然后開始按照條件比較：

當s1 的課程id 為 4 的時候， s1.course_id = s2.course_id and s1.score < s2.score