> 题目以及建表语句sql等在本系列的第一篇文章，请自取 [链接地址](https://juejin.cn/post/7242524966905937976 "https://juejin.cn/post/7242524966905937976") 11 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 ```sql SELECT * from student RIGHT JOIN (SELECT Sid,count(score) as count,avg(score) as avgScore from sc where score < 60 GROUP BY Sid HAVING count >= 2) a ON student.Sid = a.Sid -- SELECT Sid,count(score) as count,avg(score) as avgScore from sc where score < 60 -- GROUP BY Sid -- -- SELECT Sid,score from sc where score < 60 -- SELECT Sid,count(score) from sc where score < 60 GROUP BY Sid SELECT s.SId, s.Sname, AVG(sc.score) as avg_score FROM Student s JOIN SC sc ON s.SId = sc.SId WHERE sc.score < 60 GROUP BY s.SId, s.Sname HAVING COUNT(sc.CId) >= 2; ``` 12 检索" 01 "课程分数小于 60，按分数降序排列的学生信息 ```sql -- 先筛选后连接表 SELECT * FROM Student RIGHT JOIN ( SELECT * FROM SC WHERE CId = '01' AND score < 60 ) AS subquery ON Student.SId = subquery.SId; ORDER BY score DESC -- 先连接表后筛选 SELECT Student.* FROM Student INNER JOIN SC ON Student.SId = SC.SId WHERE SC.CId = '01' AND SC.score < 60 ORDER BY SC.score DESC; ``` 13 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 ```sql select * from sc left join ( select sid,avg(score) as avscore from sc group by sid )r on sc.sid = r.sid order by avscore desc; ``` 14 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 ```sql SELECT sc.Cid, course.Cname, max(sc.score)as 最高分, min(sc.score)as 最低分, AVG(sc.score)as 平均分, count(DISTINCT SC.SId)as 选修人数, sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率, sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率, sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as 优良率, sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 优秀率 from sc LEFT JOIN course ON sc.Cid = course.Cid GROUP BY course.Cid ORDER BY count(*)DESC, sc.CId ASC SELECT Course.CId AS '课程 ID', Course.Cname AS '课程 name', MAX(score) AS '最高分', MIN(score) AS '最低分', AVG(score) AS '平均分', ROUND(SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS '及格率', ROUND(SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS '中等率', ROUND(SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS '优良率', ROUND(SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS '优秀率', COUNT(DISTINCT SC.SId) AS '选修人数' FROM Course JOIN SC ON Course.CId = SC.CId GROUP BY Course.CId, Cname ORDER BY COUNT(DISTINCT SC.SId) DESC, Course.CId ASC; SELECT SC.CId AS '课程 ID', Course.Cname AS '课程 name', MAX(SC.score) AS '最高分', MIN(SC.score) AS '最低分', AVG(SC.score) AS '平均分', CONCAT(ROUND(COUNT(CASE WHEN SC.score >= 60 THEN 1 END)/COUNT(*),3)*100,'%') AS '及格率', CONCAT(ROUND(COUNT(CASE WHEN SC.score >= 70 AND SC.score < 80 THEN 1 END)/COUNT(*),3)*100,'%') AS '中等率', CONCAT(ROUND(COUNT(CASE WHEN SC.score >= 80 AND SC.score < 90 THEN 1 END)/COUNT(*),3)*100,'%') AS '优良率', CONCAT(ROUND(COUNT(CASE WHEN SC.score >= 90 THEN 1 END)/COUNT(*),3)*100,'%') AS '优秀率' FROM SC JOIN Course ON SC.CId = Course.CId GROUP BY SC.CId, Course.Cname ``` 15 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺 ```sql SELECT CId, score, ( SELECT COUNT(DISTINCT score) FROM SC as B WHERE B.CId = A.CId and B.score > A.score ) + 1 AS rank FROM SC AS A ORDER BY CId, score DESC; ``` 16 查询学生的总成绩，并进行排名，总分重复时保留名次空缺 ```sql SELECT S.SId, S.Sname, SUM(SC.score) AS student_toal_score, ( SELECT COUNT(*) + 1 FROM ( SELECT SUM(score) AS total_score FROM SC GROUP BY SId ) T WHERE T.total_score > SUM(SC.score) ) AS rank FROM Student S JOIN SC ON S.SId = SC.SId GROUP BY S.SId, S.Sname ORDER BY student_toal_score DESC; ``` 17 统计各科成绩各分数段人数：课程编号，课程名称，\[100-85]，\[85-70]，\[70-60]，\[60-0] 及所占百分比 ```sql SELECT t.cid,level,levelCount,totalCount,(levelCount/totalCount) from ( (SELECT cid,(CASE WHEN 85 sc1.score) + 1 AS rank FROM SC sc1 WHERE (SELECT COUNT(*) FROM SC sc2 WHERE sc1.CId = sc2.CId AND sc1.score < sc2.score) < 3 ) sc ON c.CId = sc.CId JOIN Student s ON sc.SId = s.SId WHERE sc.rank <= 3 ORDER BY c.Cname ASC, sc.score DESC; select a.sid,a.cid,a.score from sc a left join sc b on a.cid = b.cid and a.scorea.score)<3 order by a.Cid,a.score desc ``` 19 查询每门课程被选修的学生数 ```sql select cid, count(sid) from sc group by cid; ``` 20 查询出只选修两门课程的学生学号和姓名 ```sql select student.sid, student.sname from student where student.sid in (select sc.sid,COUNT(*) from sc group by sc.sid having count(sc.cid)=2 ); ```