MySQL经典50题
笔者最近将网上流传的MySQL数据库经典50题进行了练习,梳理了一份自己的练习成果。下图是MySQL练习题中涉及到的4张表和它们的具体字段:
- 学生表
- 课程表
- 成绩表
- 教师表
所有的题目都是根据4张表来进行出题,涉及到了很多的MySQL/SQL
的知识点,希望对想提升SQL
的读者朋友有所帮助。
如果有不对或者还可以优化的地方欢迎提出来
建表语句
4张表是自己手动创建的,具体语句如下:
插入数据
在建表之后,我们需要往每个表中插入模拟数据:
时间相关
时间相关的问题中涉及到年月日、星期、季度等的求解,同时需要注意边界问题。下面是整理的几个常见的时间处理函数:
year()
:函数返回的是年份date_format(now(), '%Y%m%d')
:返回的是当前日期的年月日dayofyear()
:该函数返回的是当前日期处于一年中的第几天weekofyear()
:该函数返回的是该日期处于一年中的第几周week()
:同样也是返回当前日期处于一年中的第几周month()
:该函数返回的是月份,1-12dayofweek()
:该函数返回的是星期索引,1代表星期1,国内的惯例weekday()
:星期索引,0代表星期1,一般是国外的惯例
-- 46、查询各学生的年龄:按照出生日期来算,当前月日 < 出生年月的月日则,年龄减1
select
s_name
,s_birth
,date_format(now(), '%Y') - date_format(s_birth, '%Y') - (case when date_format(now(), '%m%d') > date_format(s_birth, '%m%d') then 0 else 1 end) as age -- 当前日期大,说明已过年,龄正常;反之说明今年生日未到,年龄-1
from Student;
-- 47、查询本周过生的同学
select * from Student where week(date_format(now(),'%Y%m%d')) = week(s_birth);
-- 48、查询下周过生的同学
-- 需要考虑边界问题:就是可能下周刚好是明年的第一周
select *
from Student
where mod(week(now()), 52) + 1 = week(s_birth); -- mod函数求余数
-- 49、查询本月过生的同学
select *
from Student
where month(s_birth) = month(now());
-- 50、查询下月过生的同学
-- 同样需要考虑边界问题:下个月刚好是下年的第一个月
select * from Student
where mod(month(now()),12) + 1 = month(s_birth);
having子句使用
having子句的作用是将数据筛选出来之后在加上条件进行二次筛选,通常是最后执行过滤条件
-- 45、查询选修了全部课程的学生信息
select
s.*
,count(c_id) num -- 课程数目
from Score sc
left join Student s
on sc.s_id = s.s_id
group by s.s_id -- 分组
having num in (select count(*)
from Course); --满足全部课程
-- 44、查询至少选修两门课程的同学
select
s_id
,count(*) num
from Score
group by s_id
having count(*) >= 2; -- 至少两门课程
-- 43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
select
c_id
,count(s_score) num -- 统计课程数量
from Score
group by c_id
having num > 5 -- 课程数目大于5
order by num desc, c_id; -- 排序规则
-- 33、查询平均成绩大于等于75的所有学生的学号、姓名和平均成绩
select
sc.s_id
,s.s_name
,round(avg(sc.s_score),2) avg_score
from Score sc
left join Student s
on sc.s_id = s.s_id
group by sc.s_id,s.s_name
having avg_score >= 75;
-- 27、查询出只有两门课程的全部学生的学号和姓名
select
s.s_id
,s.s_name
from Student s
left join Score sc -- 连接两个表
on s.s_id = sc.s_id
group by 1,2
having count(sc.c_id) = 2; -- 分组后再过滤
-- 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select
s.s_id
,s_name
,round(avg(s_score), 2) avg_score -- 平均成绩
from Student s
left join Score sc
on s.s_id=sc.s_id
and sc.s_score < 60 -- 不及格
group by s.s_id
having count(sc.c_id )>= 2; -- 2门课
-- 11、查询没有学完全部课程的同学的信息
select s.*
from Student s -- 学生表
left join Score s1 -- 成绩表
on s1.s_id = s.s_id
group by s.s_id
having count(s1.c_id) < ( -- 分组后学生的课程数<3
select count(*) from Course -- 全部课程数=3
)
-- 4、查询平均成绩小于70分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
-- 使用NULL判断
select
a.s_id
,a.s_name
,round(AVG(b.s_score), 2) avg_score
from Student a
left join Score b
on a.s_id = b.s_id
group by a.s_id
having avg_score < 70 or avg_score is null; -- 王菊同学没有任何哼唧,需要她也考虑进来
-- 使用ifnull判断
select
S.s_id
,S.s_name
,round(avg(ifnull(C.s_score,0)), 2) as avg_score -- ifnull 函数:第一个参数存在则取它本身,否则取第二个值
from Student S
left join Score C
on S.s_id = C.s_id
group by s_id
having avg_score < 70;
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select
b.s_id
,b.s_name
,round(avg(a.s_score), 2) as avg_score
from Student b
join Score a
on b.s_id = a.s_id
group by b.s_id -- 分组后查询每个人平均成绩
having avg_score >= 60; -- 分组之后再进行过滤选择
多表连接查询
有时候我们需要的信息要通过几个表关联起来进行查询,这个时候就要用到SQL
的关联查询,主要是LEFT JOIN
方法比较多。
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select
distinct a.s_id -- 去重
,a.c_id
,a.s_score
from Score a
join Score b
on a.c_id != b.c_id -- 课程不同
and a.s_score = b.s_score -- 分数相同
and a.s_id != b.s_id; -- 学号不同
-- 30、同名同性的学生名单,并统计同名人数
select
a.s_name
,a.s_sex
,count(*)
from Student a -- 同一个表的自连接
join Student b
on a.s_id != b.s_id -- 连接的时候不能是同一个人:学号具有唯一性,其他字段可能重复
and a.s_sex = b.s_sex -- 性别、名字相同
and a.s_name = b.s_name
group by 1,2;
-- 12、查询至少有一门课与学号为01的同学所学相同的同学的信息
select s1.*
from Student s1
left join Score s2
on s1.s_id = s2.s_id -- 学生表和成绩表的关联
and c_id in (select c_id from Score where s_id=01) -- 对课程进行限制,只在01学生的课程内
group by s1.s_id; -- 根据学号分组
-- 9、查询学过编号为01和02课程的学生信息
-- 通过自连接来实现
select s1.*
from Student s1
where s_id in (
select s2.s_id from Score s2
left join Score s3
on s2.s_id=s3.s_id
where s2.c_id='01' and s3.c_id='02'
);
-- 7、查询学过李四老师授课的同学的信息
select s.*
from Teacher t -- 4张表全部连接起来查询
left join Course c on t.t_id=c.t_id
left join Score sc on c.c_id=sc.c_id
left join Student s on s.s_id=sc.s_id
where t.t_name='李四';
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select
a.*
,b.s_score as 1_score
,c.s_score as 2_score
from Student a
left join Score b
on a.s_id=b.s_id and b.c_id='01' or b.c_id=NULL -- 包含NULL的数据
left join Score c
on a.s_id=c.s_id and c.c_id='02'
where b.s_score < c.s_score;
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select
a.*
,b.s_score as 1_score
,c.s_score as 2_score
from Student a
join Score b on a.s_id = b.s_id and b.c_id = '01' -- 通过学号连接,指定01
left join Score c on a.s_id = c.s_id and c.c_id='02' or c.c_id is NULL -- 指定02,或者c中的c_id直接不存在
-- 为NULL的条件可以不存在,因为左连接中会直接排除c表中不存在的数据,包含NULL
where b.s_score > c.s_score;
排名问题(重点)
在SQL的提数需求中经常会遇到求解前几名,或者前几个数据的需要。由于MySQL 5.X中没有开窗函数,所以我们只能通过自己写SQL脚本来解决排名问题。
在MySQL8.0中则可以直接使用窗口函数来求解。
-- 42、查询每门功课成绩最好的前3名
select
a.c_id
,a.s_id
,a.s_score
from Score a
where (select count(1) -- count(1)类似count(*):统计表b中分数大的数量
from Score b
where b.c_id=a.c_id -- 课程相同
and b.s_score >= a.s_score) <= 3 -- 前3名
order by a.c_id;
-- 25、各科成绩的前3名
select
a.s_id
,a.c_id
,a.s_score
from Score a -- 同一个表的自连接
join Score b
on a.c_id = b.c_id
and a.s_score <= b.s_score -- 判断a的分数小于等于b的分数,要带上等号
group by 1,2,3
having count(b.s_id) <= 3 -- b中的个数至少有3个,等号用来应对分数相同情形
order by 2, 3 desc; -- 课程升序,成绩降序
-- 24、查询每个学生的平均成绩及名次
select
t1.s_id
,t1.s_name
,t1.avg_score
,(select count(distinct t2.avg_score)
from (select
sc.s_id
,s.s_name
,round(avg(sc.s_score),2) avg_score
from Score sc
join Student s
on sc.s_id=s.s_id
group by sc.s_id,s.s_name)t2 -- 临时表t2同t1
where t2.avg_score >= t1.avg_score
) rank
from (select
sc.s_id
,s.s_name
,round(avg(sc.s_score),2) avg_score
from Score sc
join Student s
on sc.s_id=s.s_id
group by sc.s_id,s.s_name)t1 -- 临时表t1:每个人的平均成绩
order by t1.avg_score desc;
-- 22、所有课程的成绩第2名到第3名的学生信息及该课程成绩
-- 找出各科成绩2-3名再进行拼接,方法有待优化!!!
(select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '语文'
order by s.s_score desc
limit 1, 2)
union
(select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '数学'
order by s.s_score desc
limit 1, 2)
union
((select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '英语'
order by s.s_score desc
limit 1, 2))
-- 20、查询学生的总成绩,同时进行排名
select
t1.s_id ,t1.s_name, t1.score
,(select count(t2.score)
from(select s.s_id, s.s_name, sum(sc.s_score) score
from Student s
join Score sc on s.s_id = sc.s_id
group by s.s_id
order by 3 desc)t2 -- 总成绩的降序排列,只是没有排名;
where t2.score > t1.score) + 1 as rank
from(
select s.s_id ,s.s_name ,sum(sc.s_score) score
from Student s
join Score sc on s.s_id = sc.s_id
group by s.s_id
order by 3 desc)t1 -- 总成绩的降序排列,只是没有排名;t2和t1相同
order by 3 desc;
-- 19、按照各科成绩进行排序,并且显示排名
select * from (select
t1.c_id,
t1.s_score,
(select count(distinct t2.s_score)
from Score t2
where t2.s_score>=t1.s_score and t2.c_id='01') rank
from Score t1 where t1.c_id='01'
order by t1.s_score desc) t1
union
select * from (select
t1.c_id
,t1.s_score
,(select count(distinct t2.s_score)
from Score t2
where t2.s_score>=t1.s_score and t2.c_id='02') rank
from Score t1 where t1.c_id='02'
order by t1.s_score desc) t2
union
select * from (select
t1.c_id,
t1.s_score,
(select count(distinct t2.s_score) from Score t2 where t2.s_score>=t1.s_score and t2.c_id='03') rank
from Score t1 where t1.c_id='03'
order by t1.s_score desc) t3
上面第19题的结果如下图:
最值问题
经常会在实际的工作需求中遇到最值问题的求解。
-- 40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
select
s.*
,sc.s_score
,sc.c_id
,c.c_name
from Student s -- 学生表
left join Score sc -- 成绩表
on s.s_id = sc.s_id
left join Course c -- 课程表
on sc.c_id = c.c_id
where sc.s_score in (
select max(sc.s_score) -- 找出张三老师教授的课程中的最大分值
from Score sc
left join Course c
on sc.c_id = c.c_id
left join Teacher t
on c.t_id = t.t_id
where t.t_name = '张三');
统计count
在实际需求中我们需要统计 一些个数或者人数,用到的是count函数
-- 39:每门课程的学生人数
select
c_id
,count(s_id) -- 统计数目
from Score
group by c_id;
-- 28、查询男女人数
select
s_sex
,count(s_sex) as `人数`
from Student
group by s_sex; -- 性别分组
-- 26、每门课程的选修人数
select
c.c_id
,c.c_name
,count(s.s_id) -- 统计学生人数
from Course c
left join Score s
on c.c_id = s.c_id
group by c.c_id; -- 课程号分组
where语句
where语句的功能是加入条件进行过滤选择。where是先过滤再进行选择筛选,having是完成分组聚合之后再进行过滤。
-- 38、查询课程编号为02且课程成绩大于等于75的学生的学号和姓名
select
sc.s_id
,s.s_name
,sc.s_score
from Score sc -- 成绩表
join Student s -- 学生信息表
on sc.s_id = s.s_id
join Course c -- 课程表
on sc.c_id = c.c_id
where c.c_id = 02
and sc.s_score >= 75;
-- 37、查询不及格的课程
select
sc.c_id
,c.c_name
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where sc.s_score < 60;
-- 36、查询任何一门课程成绩都在75分以上的学生姓名、课程名和分数
select
s.s_name
,c.c_name
,sc.s_score
from Score sc -- 成绩表
left join Student s -- 学生信息表
on sc.s_id = s.s_id
left join Course c -- 课程表
on sc.c_id = c.c_id
where sc.s_score > 75
group by s.s_name, c.c_name, sc.s_score;
-- 34、课程名称为语文,且分数低于70的学生姓名和分数
select
s.s_name
,sc.s_score
from Score sc -- 成绩表
join Student s -- 学生信息表
on sc.s_id = s.s_id
join Course c -- 课程表,指定语文
on sc.c_id = c.c_id
where c.c_name = '语文'
and sc.s_score < 70; -- 指定成绩
-- 16、检索02课程分数小于70,按分数降序排列的学生信息
select
s.*
,sc.s_score
from Student s
join Score sc
on s.s_id=sc.s_id
where sc.c_id=02 and sc.s_score < 70
order by sc.s_score desc; -- 指定为降序方式desc
-- 14、查询没有修过李四老师讲授的任何一门课程的学生姓名
-- 一步步得到结果:老师姓名--->老师编号--->课程号---> 学号--->学生姓名
select s_name -- 4、学号取反找到学生姓名
from Student
where s_id not in(
select distinct(s_id) -- 3、课程号找到对应的学号
from Score
where c_id=(
select c_id -- 2、教师编号找到对应的课程号
from Course
where t_id=(
select t_id -- 1、姓名找到教师编号
from Teacher
where t_name='李四')
));
-- 13、查询和01同学学习的课程完全相同的同学的信息
select *
from Student
where s_id in (
select s_id -- 3、步骤2中得到的学号是满足要求的
from(select
distinct(s_id)
,count(c_id) number
from Score
group by s_id)t1 -- 1、学号和所修课程分组的结果t1
where number=3 -- 2、投机取巧:选择出所修课程数是3(01修了3门)的学号
and s_id !=01); -- 01学生需要本身排除
-- 12、查询至少有一门课与学号为01的同学所学相同的同学的信息
select * -- 3、求出学生信息
from Student
where s_id in (
select distinct s_id -- 2、找出满足课程在01学生课程中的全部学生,学号去重,同时将01自己排除
from Score
where c_id in (
select c_id
from Score
where s_id=01) -- 1、找出学号01同学的全部课程
and s_id != 01);
-- 11、查询没有学完全部课程的同学的信息
select * -- 排除学号后得到的结果
from Student
where s_id not in (select s_id from (select s_id, count(s_id) as number -- 3.最大课程数所在的学号需要排除
from Score
group by s_id) s -- 取别名
where number=(select max(number) -- 2.保证最大的课程数
from( select s_id, count(s_id) as number -- 1.学号和个数统计(即修了几门课)
from Score group by s_id)t)); -- 别名
-- 10、查询学过01课程,但没有学过02课程的学生信息
select s1.*
from Student s1
where s1.s_id in (select s_id from Score where c_id='01') -- 修过01课程,要保留
and s1.s_id not in (select s_id from Score where c_id='02'); -- 哪些人修过02,需要排除
-- 9、查询学过01并且学过02课程的学生信息
select s1.*
from Student s1, Score s2, Score s3
where s1.s_id=s2.s_id -- 一个表使用两次
and s1.s_id=s3.s_id
and s2.c_id=01 and s3.c_id=02;
-- 8、找出没有学过李四老师课程的学生
select * -- 3. 通过学号找出全部学生信息
from Student
where s_id not in ( -- 2.学号取反:不在李四老师授课的学生的学号中
select s_id
from Score S
left join Course C
on S.c_id = C.c_id
where C.t_id=(select t_id from Teacher where t_name ="李四") -- 1.查询李四老师的课程
);
-- 7、查询学过李四老师授课的同学的信息
select s1.*
from Student s1
join Score s2
on s1.s_id=s2.s_id
where s2.c_id in (
select c_id from Course c where t_id=( -- 1. 通过老师找出其对应的课程
select t_id from Teacher t where t_name="李四")
)
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select
a.*
,b.s_score as score_1
,c.s_score as score_2
from Student a, Score b, Score c
where a.s_id=b.s_id -- 同一个学生
and a.s_id=c.s_id
and b.c_id='01' -- 不同的两门课程
and c.c_id='02'
and b.s_score < c.s_score; -- 课程01分数比02分数低
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select
a.*
,b.s_score as score_1
,c.s_score as score_2
from Student a, Score b, Score c
where a.s_id=b.s_id -- 同一个学生
and a.s_id=c.s_id
and b.c_id='01' -- 两门课程
and c.c_id='02'
and b.s_score > c.s_score; -- 前者成绩高
分组聚合
分组聚合统计在SQL中也是很常见的,需要用到group by和sum、max、min、count等聚合函数
-- 35、所有学生的课程及分数情况
select
s.s_id
,s.s_name
,sum(case c.c_name when '语文' then sc.s_score else 0 end) as '语文' -- 语文分数
,sum(case c.c_name when '数学' then sc.s_score else 0 end) as '数学'
,sum(case c.c_name when '英语' then sc.s_score else 0 end) as '英语'
,sum(sc.s_score) as '总分' -- 每个人的总分
from Student s
left join Score sc
on s.s_id = sc.s_id
left join Course c
on sc.c_id = c.c_id
group by s.s_id, s.s_name; -- 学号和姓名的分组
-- 33、平均成绩大于等于75的所有学生的学号、姓名和平均成绩
select
sc.s_id
,s.s_name
,round(avg(sc.s_score),2) avg_score -- 平均函数聚合
from Score sc
join Student s
on sc.s_id = s.s_id
group by sc.s_id,s.s_name
having avg_score >= 75;
-- 32、每门课程的平均成绩,结果按平均成绩降序排列;平均成绩相同时,按课程编号c_id升序排列
select
c_id
,round(avg(s_score),2) avg_score -- 平均函数聚合
from Score
group by 1
order by 2 desc, c_id; -- 指定字段和排序方法
-- 21、查询不同老师所教不同课程平均分从高到低显示
select
c.c_name
,t.t_name
,round(avg(s.s_score),2) score -- 课程分组后再求均值
from Course c -- 主表,通过两次连接
left join Teacher t
on c.t_id = t.t_id
left join Score s
on c.c_id = s.c_id
group by c.c_id -- 课程
order by 3 desc; -- 降序
-- 21、查询不同老师所教不同课程平均分从高到低显示
select
c.c_name
,t.t_name
,round(avg(s.s_score),2) score -- 课程分组后再求均值
from Course c -- 课程表
left join Teacher t -- 教师表
on c.t_id = t.t_id
left join Score s -- 成绩表
on c.c_id = s.c_id
group by c.c_id -- 课程编号
order by 3 desc; -- 分组后排序,降序方式
-- 18、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率;及格:>=60,中等为:70-80,优良为:80-90,优秀为:>=90
select
s.c_id
,c.c_name
,max(s.s_score) -- 最高分
,min(s.s_score) -- 最低分
,round(avg(s.s_score), 2) -- 平均分
,round(100 * (sum(case when s.s_score >= 60 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 及格率
,round(100 * (sum(case when s.s_score >= 70 and s.s_score <= 80 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 中等率
,round(100 * (sum(case when s.s_score >= 80 and s.s_score <= 90 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 优良率
,round(100 * (sum(case when s.s_score >= 90 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 优秀率
from Score s
left join Course c
on s.c_id = c.c_id
group by s.c_id, c.c_name; -- 分组
-- 17、按平均成绩从高到低(降序)显示所有学生的所有课程的成绩以及平均成绩
select
s.s_id
,s.c_id
,s.s_score
,t.avg_score
from Score s
left join (
select
s_id
,round(avg(s_score),2) avg_score
from Score
group by s_id)t -- 中间表求出学号和平均分
on s.s_id = t.s_id -- 通过学号进行关联
order by 4 desc;
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select
a.s_id
,a.s_name
,count(b.c_id) as course_number -- 课程数
,sum(b.s_score) as scores_sum -- 总成绩
from Student a
left join Score b
on a.s_id = b.s_id
group by a.s_id,a.s_name;
模糊匹配
在SQL
中模糊匹配使用的关键字是like
,符号是%
-- 31、1991年出生的学生信息
select *
from Student
where s_birth like '1991%'; -- 模糊匹配like关键字
-- 29、名字中带有“云”的学生信息
-- 我们使用左右匹配:考虑到有人可能姓云(虽然很少)
select * from Student where s_name like "%云%";
-- 6、查询“李”姓老师的数量
select count(t_name) from Teacher where t_name like "李%"; -- 模糊查询和通配符的使用
case语句
case语句用来进行条件判断,下图中介绍了SQL中的两种case表达式的写法,来自《SQL进阶教程》一书:
-- 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
-- case表达式若为真则为1,并进行sum求和操作
select
s.c_id
,c.c_name
,sum(case when s_score > 85 and s_score <=100 then 1 else 0 end) as '85-100'
,round(100 * (sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(*)), 2) '[85,100]占比'
,sum(case when s_score > 70 and s_score <=85 then 1 else 0 end) as '70-85'
,round(100 * (sum(case when s_score > 70 and s_score <= 85 then 1 else 0 end) / count(*)), 2) '[70,85]占比'
,sum(case when s_score > 60 and s_score <=70 then 1 else 0 end) as '60-70'
,round(100 * (sum(case when s_score > 60 and s_score <= 70 then 1 else 0 end) / count(*)), 2) '[60,70]占比'
,sum(case when s_score >=0 and s_score <=60 then 1 else 0 end) as '0-60'
,round(100 * (sum(case when s_score > 0 and s_score <= 60 then 1 else 0 end) / count(*)), 2) '[0,60]占比'
from Score s
left join Course c
on s.c_id = c.c_id
group by s.c_id, c.c_name; -- 分课程统计总数和占比
-- 18、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率;及格:>=60,中等为:70-80,优良为:80-90,优秀为:>=90
select
s.c_id
,c.c_name
,max(s.s_score)
,min(s.s_score)
,round(avg(s.s_score), 2)
,round(100 * (sum(case when s.s_score >= 60 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 及格率 -- case语句使用
,round(100 * (sum(case when s.s_score >= 70 and s.s_score <= 80 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 中等率
,round(100 * (sum(case when s.s_score >= 80 and s.s_score <= 90 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 优良率
,round(100 * (sum(case when s.s_score >= 90 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 优秀率
from Score s
left join Course c
on s.c_id = c.c_id
group by s.c_id, c.c_name;