经典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
<span class="hljs-nomarkup">ORDER</span> 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
) <span class="hljs-section">< 3
ORDER BY cid,score desc</span>
```
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
```
后续还有场景再补充。。。
评论区