【MySQL】求每门科目成绩排名前二的学生信息

小半 • 2023年1月12日下午2:58 • 技术随笔 • 阅读 113

建表语句如下：
学生表

CREATE TABLE student
		(sno VARCHAR(4) NOT NULL primary key, 
		sname VARCHAR(4) NOT NULL,
		age VARCHAR(3))ENGINE=INNODB  DEFAULT CHARSET=utf8 ;

成绩表

CREATE TABLE score 
		(sno VARCHAR(4) NOT NULL, 
		kemu VARCHAR(5) NOT NULL, 
		degree NUMERIC(10, 1) NOT NULL,
		foreign  key(sno) references  student(sno)
		)ENGINE=INNODB DEFAULT CHARSET=utf8 ;

-- 插入数据
insert into student values(1001,'kb',18),(1002,'kd',19),(1003,'love',18),(1004,'wade',20),(1005,'jame',18),(1006,'bull',22),(1007,'nets',26),(1008,'suns',29)
 
-- 插入数据
insert into score values(1001,'语文',81),(1002,'语文',85),(1001,'数学',100),(1002,'英语',88),(1003,'py',99),(1004,'语文',70),(1001,'英语',98),(1002,'py',87),(1002,'数学',70),(1004,'py',85),(1004,'数学',56),(1001,'py',78)

成绩后的表如下所示：

如果要查找的字段在同一张表中

例如：查找每门科目成绩排名前二的学生信息，显示学号，科目，分数（单表查询）

1、先取一个新表sc1，左连接至原表，对degree栏做比较，条件为sc1.kemu=sc2.kemu and sc1.degree<sc2.degree，其实就是列出同一门课内所有分数比较的情况。出现两次及以下，说明在这个科目中处于前2排名

select sc1.sno,sc1.kemu,sc1.degree
from score sc1
where (select count(1) from score sc2 where sc1.kemu=sc2.kemu and sc1.degree<sc2.degree)<2
order by sc1.kemu,sc1.degree desc

如果要查找的字段不在同一张表中

例如：取出符合每门科目成绩排名前二的信息,显示学号，姓名（在student表中），科目，分数（多表查询）

1、先在score表中取出符合每门科目成绩排名前二的信息，形成一个新表a；然后对表a与student表进行关联，输出student和c表的需求列，得出结果。

select student.sno,student.sname,a.kemu,a.degree
from student
inner join
(select sc1.sno,sc1.kemu,sc1.degree
from score sc1
where (select count(1) from score sc2 where sc1.kemu=sc2.kemu and sc1.degree<sc2.degree)<2 order by sc1.kemu,sc1.degree desc) as a
on student.sno=a.sno
order by a.kemu,a.degree desc