经典mysql面试题 - 学生成绩

现有 student(sid.sname)学生表，course(cid,cname)课程表，sc(sid,cid,score)成绩表。

CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `sc` (
  `scid` int(11) NOT NULL AUTO_INCREMENT,
  `sid` int(11) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `score` float(5,2) DEFAULT NULL,
  PRIMARY KEY (`scid`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4;

1、查询各科平均戊绩，格式如下:cid,cname,avg_score

SELECT AVG(sc.score) as avg_score,sc.cid,course.cname
FROM `sc`
LEFT JOIN course on sc.scid = course.cid
GROUP BY sc.cid

2、直询总分前3的学生数据，格式如下:sid,sname,total_score

SELECT SUM(sc.score) as total_score ,sc.sid,student.sname
FROM sc
left JOIN  student on sc.sid = student.sid
GROUP BY sc.sid
ORDER BY total_score desc limit 3

3.查询各科前三名学生

select sc.score,sc.cid,sc.sid,course.cname,student.sname from sc 
LEFT JOIN course on sc.cid = course.cid
LEFT JOIN student on sc.sid = student.sid
where (
select count(*)
from
sc b
where sc.cid = b.cid
and sc.score < b.score
) < 3
ORDER BY cid,score desc

4.查询各科都及格学生

select distinct sc.sid,student.sname
from sc 
LEFT join student on sc.sid = student.sid
where sc.sid not in (select distinct sid from sc where score<=60)

5，各科成绩超过平均分的同学，格式如下:sid,sname，score，cid，cname，avg_score

SELECT
	s.sid,
	s.sname,
	sc.score,
	sc.cid,
	c.cname,
	avg_score
FROM
	sc
	LEFT JOIN ( SELECT cid, AVG( score ) avg_score FROM sc GROUP BY cid ) tmp ON sc.cid = tmp.cid
	LEFT JOIN student s ON s.sid = sc.sid 
	LEFT join course c on c.cid = sc.cid 
WHERE
	sc.score > tmp.avg_score

6，各科成绩都超过平均分的同学，格式如下:sid,sname

SELECT
	s.sid,
	s.sname
FROM
	sc
	LEFT JOIN ( SELECT cid, AVG( score ) avg_score FROM sc GROUP BY cid ) tmp ON sc.cid = tmp.cid
	LEFT JOIN student s ON s.sid = sc.sid 
WHERE
	sc.score > tmp.avg_score
GROUP BY sc.sid
HAVING COUNT(s.sid) = (select COUNT(*) from course)

6、统计并列出各科成绩、各分数段入数;课程ID,课程名称,[100-90],[89-70],[69-60][ <60]

select 
sc.cid,
course.cname,
count(case when sc.score >= 90 then 1 end)  as  '[100-90]',
count(case when sc.score < 90 and sc.score >=70  then 1 end)  as  '[89-70]',
count(case when sc.score < 70 and sc.score >=60 then 1 end)  as  '[69-60]',
count(case when sc.score<60 then 1 end)  as  '[59-0]'
FROM sc
left JOIN course on sc.cid = course.cid
GROUP BY sc.cid

后续还有场景再补充。。。