【MySQL】求每门科目成绩排名前二的学生信息
建表语句如下:
学生表
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