编程知识 cdmana.com

MySQL50题-分类总结

MySQL经典50题

笔者最近将网上流传的MySQL数据库经典50题进行了练习,梳理了一份自己的练习成果。下图是MySQL练习题中涉及到的4张表和它们的具体字段:

  • 学生表
  • 课程表
  • 成绩表
  • 教师表

所有的题目都是根据4张表来进行出题,涉及到了很多的MySQL/SQL的知识点,希望对想提升SQL的读者朋友有所帮助。

如果有不对或者还可以优化的地方欢迎提出来

建表语句

4张表是自己手动创建的,具体语句如下:

插入数据

在建表之后,我们需要往每个表中插入模拟数据:

时间相关

时间相关的问题中涉及到年月日、星期、季度等的求解,同时需要注意边界问题。下面是整理的几个常见的时间处理函数:

  • year():函数返回的是年份
  • date_format(now(), '%Y%m%d') :返回的是当前日期的年月日
  • dayofyear() :该函数返回的是当前日期处于一年中的第几天
  • weekofyear():该函数返回的是该日期处于一年中的第几周
  • week():同样也是返回当前日期处于一年中的第几周
  • month():该函数返回的是月份,1-12
  • dayofweek():该函数返回的是星期索引,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;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

版权声明
本文为[皮大大]所创,转载请带上原文链接,感谢
https://cloud.tencent.com/developer/article/1794952

Scroll to Top