11. 查询两门及以上不及格课程的同学的学号，姓名及平均成绩
第一步：锁定表：学生表，成绩表
第二步：查询不及格课程的同学成绩表
SELECT *
FROM `sc`
WHERE `score` < 60
第三步：计数不及格同学不及格课程数量，并筛选出不及格课程超过两门的
SELECT a.`SId`,COUNT(a.`SId`),AVG(a.`score`) AS 平均成绩
FROM(SELECT *
FROM `sc`
WHERE `score` < 60) a
GROUP BY a.`SId`
HAVING COUNT(a.`SId`)>=2
第四步：连表学生表，查询查询两门及以上不及格课程的同学的学号，姓名及平均成绩
SELECT b. `SId`,st.`Sname`,平均成绩
FROM (SELECT a.`SId`,COUNT(a.`SId`),AVG(a.`score`) AS 平均成绩
FROM(SELECT *
FROM `sc`
WHERE `score` < 60) a
GROUP BY a.`SId`
HAVING COUNT(a.`SId`)>=2) b
INNER JOIN `student` st
ON b.`SId`=st.`SId`
或者这样，减少一次子查询
SELECT a.`SId`,st.`Sname`,AVG(a.`score`) AS 平均成绩
FROM(SELECT *
FROM `sc`
WHERE `score` < 60) a
INNER JOIN `student` st
ON a.`SId`=st.`SId`
GROUP BY a.`SId`
HAVING COUNT(a.`SId`)>=2
12.检索01课程分数小于60分，按分数降序排列的学生信息（涉及排序）
第一步：锁定表：成绩表和学生表
第二步：筛选出01课程分数小于60的学生id
SELECT `SId`,`score`
FROM `sc`
WHERE `score`<60 AND `CId`='01'
第三步：连表学生表，并按分数将序排列（加一列成绩）
SELECT st.*,a.`score`
FROM `student` st
INNER JOIN (SELECT `SId`,`score`
FROM `sc`
WHERE `score`<60 AND `CId`='01') a
ON st.`SId`=a.`SId`
ORDER BY a.`score` DESC
13.按平均成绩从高到低显示所有学生的所有课程的成绩及平均成绩（涉及排序）
关键词：所有学生，所有课程的成绩，平均成绩
第一步：锁定表：学生表，成绩表
第二步：查询有成绩学生所有课程平均成绩-建新表
SELECT `SId`,AVG(`score`)
FROM `sc`
GROUP BY `SId`
第三步：连表成绩表
SELECT sc.*,平均分
FROM (SELECT `SId`,AVG(`score`) AS 平均分
FROM `sc`
GROUP BY `SId`) a
LEFT JOIN `sc` sc
ON a.`SId`=sc.`SId`
ORDER BY 平均分 DESC
第四步：连表学生表，使其包含没有参加考试的学生
SELECT st.`SId`,b.`CId`,b.`score`,平均分
FROM `student` st
LEFT JOIN (SELECT sc.*,平均分
FROM (SELECT `SId`,AVG(`score`) AS 平均分
FROM `sc`
GROUP BY `SId`) a
LEFT JOIN `sc` sc
ON a.`SId`=sc.`SId`) b
ON st.`SId`=b.`SId`
ORDER BY 平均分 DESC
14. 查询各科成绩最高分，最低分和平均分
以如下形式显示：课程ID，课程name,最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为：>=60，中等为【70,80），优良为【80,90），优秀为>=90
要求输出成绩课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排序
第一步：锁定表：成绩表，课程表
第二步：筛选出成绩表中的选课人数
SELECT `CId`,COUNT(`SId`)
FROM `sc`
GROUP BY `CId`
第三步：显示课程ID，选修人数，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
SELECT `CId`,
COUNT(`SId`) AS 选课人数,
MAX(`score`) AS 最高分,
MIN(`score`) AS 最低分,
AVG(`score`) AS 平均分,
SUM(CASE WHEN `score`>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格率,
SUM(CASE WHEN `score`>=70 AND `score`<80 THEN 1 ELSE 0 END)/COUNT(*) AS 中等率,
SUM(CASE WHEN `score`>=80 AND `score`<90 THEN 1 ELSE 0 END)/COUNT(*) AS 优良率,
SUM(CASE WHEN `score`>=90 THEN 1 ELSE 0 END)/COUNT(*) AS 优秀率
FROM `sc`
GROUP BY `CId`
第四步：排序
SELECT `CId`,
COUNT(`SId`) AS 选课人数,
MAX(`score`) AS 最高分,
MIN(`score`) AS 最低分,
AVG(`score`) AS 平均分,
SUM(CASE WHEN `score`>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格率,
SUM(CASE WHEN `score`>=70 AND `score`<80 THEN 1 ELSE 0 END)/COUNT(*) AS 中等率,
SUM(CASE WHEN `score`>=80 AND `score`<90 THEN 1 ELSE 0 END)/COUNT(*) AS 优良率,
SUM(CASE WHEN `score`>=90 THEN 1 ELSE 0 END)/COUNT(*) AS 优秀率
FROM `sc`
GROUP BY `CId`
ORDER BY 选课人数 DESC,`CId` ASC;
第五步：还需要显示课程名称，连表课程表
SELECT a.*,c.`Cname`
FROM (SELECT `CId`,
COUNT(`SId`) AS 选课人数,
MAX(`score`) AS 最高分,
MIN(`score`) AS 最低分,
AVG(`score`) AS 平均分,
SUM(CASE WHEN `score`>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格率,
SUM(CASE WHEN `score`>=70 AND `score`<80 THEN 1 ELSE 0 END)/COUNT(*) AS 中等率,
SUM(CASE WHEN `score`>=80 AND `score`<90 THEN 1 ELSE 0 END)/COUNT(*) AS 优良率,
SUM(CASE WHEN `score`>=90 THEN 1 ELSE 0 END)/COUNT(*) AS 优秀率
FROM `sc`
GROUP BY `CId`
ORDER BY 选课人数 DESC,`CId` ASC) a
LEFT JOIN `course` c
ON a.`CId`=c.`CId`
15. 按各科成绩进行排序，并显示排名，score重复时也继续排名(有问题)
知识点：row_number(),rank(),dense_rank()
(1)row_number():依次排序，不会出现相同排名
(2)rank():出现相同排名时，跳跃排序
(3)dense_rank():出现相同排名时，连续排序
SELECT *,
row_number() over (GROUP BY `CId` ORDER BY `score` DESC) AS 排名
FROM `sc`;
15.1 按各科成绩进行排序，并显示排名，score重复时合并名次
SELECT *,
rank() over (PARTITION BY `CId` ORDER BY `score` DESC) AS 排名
FROM `sc`;
16. 查询学生的总成绩，并进行排名，总分重复时排名时保留名次空缺
SELECT *,
rank() over (ORDER BY 总成绩 DESC) AS 排名
FROM (SELECT `SId`,SUM(`score`) AS 总成绩
FROM `sc`
GROUP BY `SId`
) a;
16.1 查询学生的总成绩，并进行排名，总分重复时排名时保留名次空缺
SELECT *,
dense_rank() over (ORDER BY 总成绩 DESC) AS 排名
FROM (SELECT `SId`,SUM(`score`) AS 总成绩
FROM `sc`
GROUP BY `SId`
) a;
17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85),[85-70),[70-60),[60-0]及所占百分比
第一步：锁定表：成绩表，课程表
第二步：使用case when,按照
SELECT sc.`CId`,
c.`Cname`,
SUM(CASE WHEN `score`>85 THEN 1 ELSE 0 END)/COUNT(*) AS '[100-85)',
SUM(CASE WHEN `score`>70 AND `score`<=85 THEN 1 ELSE 0 END)/COUNT(*) AS '[85-70)',
SUM(CASE WHEN `score`>60 AND `score`<=70 THEN 1 ELSE 0 END)/COUNT(*) AS '[70-60)',
SUM(CASE WHEN `score`<=60 THEN 1 ELSE 0 END)/COUNT(*) AS '[60-0]'
FROM `sc` sc
INNER JOIN `course` c
ON sc.`CId`=c.`CId`
GROUP BY `CId`
第三步：使用concat拼接，输出百分比符号
SELECT sc.`CId`,
c.`Cname`,
CONCAT(SUM(CASE WHEN `score`>85 THEN 1 ELSE 0 END)/COUNT(*)*100,'%') AS '[100-85)',
CONCAT(SUM(CASE WHEN `score`>70 AND `score`<=85 THEN 1 ELSE 0 END)/COUNT(*)*100,'%') AS '[85-70)',
CONCAT(SUM(CASE WHEN `score`>60 AND `score`<=70 THEN 1 ELSE 0 END)/COUNT(*)*100,'%') AS '[70-60)',
CONCAT(SUM(CASE WHEN `score`<=60 THEN 1 ELSE 0 END)/COUNT(*)*100,'%') AS '[60-0]'
FROM `sc` sc
INNER JOIN `course` c
ON sc.`CId`=c.`CId`
GROUP BY `CId`
18. 查询各科成绩前三名的记录(mysql8.0)
第一步：锁定表：成绩表，
第二步：排名
SELECT *
dense_rank() over (PARTITION BY `CId` ORDER BY `score` DESC) AS score_rank
FROM `sc`
第三步：筛选出前三名
SELECT *
FROM(SELECT *
dense_rank() over (PARTITION BY `CId` ORDER BY `score` DESC) AS score_rank
FROM `sc`) a
WHERE score_rank <=3
