MySQL经典50题-第16到20题

作者: 皮皮大 | 来源:发表于2021-03-19 10:13 被阅读0次

MySQL50-6-第16-20题

本文中介绍的是第16-20题，涉及到的知识点包含：

自连接
SQL实现排序
多表查询

题目是：

检索01课程分数小于60，按分数降序排列的学生信息
按平均成绩从高到低（降序）显示所有学生的所有课程的成绩以及平均成绩
查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率；及格：>=60，中等为：70-80，优良为：80-90，优秀为：>=90
按照各科成绩进行排序，并且显示排名
查询学生的总成绩，并进行排名

image

题目16

题目需求

检索01课程分数小于60，按分数降序排列的学生信息

分析过程

01课程分数：Score——c_id,s_score,s_id

学生信息：Student-------s_id,s_name,s_sex,s_birth

SQL实现

自己的方法如下：

首先从Score表中找出哪些学生是满足这个要求：

select 
    s_id
    ,s_score  
from Score 
where s_score < 60 
and c_id = 01;

image

然后直接将上面的结果和Student表查询：

select s.*
from Student s
where s.s_id in (
  select 
    s_id
    ,s_score
    from Score 
    where s_score < 60 
    and c_id = 01
);

image

select 
    s.*
    ,t.s_score 
from Student s 
join (select s_id,s_score   -- 2、Student和t的连接查询
      from Score 
      where s_score < 60  
      and c_id = 01 )t   -- 1、将第一步结果作为中间表t
on s.s_id=t.s_id;

image

-- 自己的方法2：两个表的直接连接查询+where条件
select 
    s.*
    ,sc.s_score
from Student s
join Score sc
on s.s_id=sc.s_id
where sc.c_id=01 and sc.s_score < 60 
order by sc.s_score desc;  -- 默认就是降序desc

image

题目17

题目需求

按平均成绩从高到低（降序）显示所有学生的所有课程的成绩以及平均成绩

分析过程

1、平均成绩：Score表中按照学号分组查询

2、将上面步骤的结果和Score表在进行连接查询

SQL实现

下面是自己的解法：

1、先求出每个同学的平均分，并降序排列

select 
    s_id
    ,round(avg(s_score),2) avg_score 
from Score 
group by s_id 
order by 2 desc;

image

-- 自己的方法
select 
    s.s_id
    ,s.c_id
    ,s.s_score
    ,t.avg_score
from Score s
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;  -- 指的是第4个字段

image

-- 参考方法1
select 
    s.s_id
    ,(select s_score from Score where s_id=s.s_id and c_id='01')
     as 语文
     ,(select s_score from Score where s_id=s.s_id and c_id='02')
     as 数学
     ,(select s_score from Score where s_id=s.s_id and c_id='03')
     as 英语
     ,round(avg(s_score),2) 平均分 
from Score s
group by s.s_id
order by 5 desc;

image

select 
    s.s_id
    ,max(case s.c_id when '01' then s.s_score end) 语文
    ,max(case s.c_id when '02' then s.s_score end) 数学
    ,max(case s.c_id when '03' then s.s_score end) 英语
    ,avg(s.s_score)
    ,b.s_name  -- 没有出现在group by子句中，导致报错
join Student b
on s.s_id = b.s_id
group by s.s_id
order by 5 desc;

严格模式的报错：

ERROR 1055 (42000): Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.b.s_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。

-- 参考方法2：将上面的b.s_name去掉
 
 select 
  s.s_id
  ,max(case s.c_id when '01' then s.s_score end) 语文
  ,max(case s.c_id when '02' then s.s_score end) 数学
  ,max(case s.c_id when '03' then s.s_score end) 英语
  ,round(avg(s.s_score),2)  avg_score
 from Score s 
 join Student b
 on s.s_id = b.s_id
 group by s.s_id
 order by 5 desc;

image

题目18

题目需求

查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率；及格：>=60，中等为：70-80，优良为：80-90，优秀为：>=90

分析过程

成绩表：Score，查询最高分、最低分和平均分

课程表：Course，课程ID，课程name

SQL实现

思路清晰：统计每个阶段的总人数，再除以总共的人数即可

将成绩表和课程表联合起来进行查询：

case 语句用于对每个分数贴标签
sum 语句对相应的语句中的1进行求和

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;

image

题目19

题目需求

按照各科成绩进行排序，并且显示排名

分析过程

题目的意思是：将每科的成绩单独进行排名，类似如下的效果：

课程名	分数	排名
英语	99	1
英语	92	2
英语	89	3
数学	88	1
数学	85	2
……	……	…...

SQL实现

第一步：我们对Score表中的一门课程进行排名，比如01课程

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   -- SQL实现排序
      and t2.c_id = '01') rank
  from Score t1   -- 通过相同的表实现自连接
  where t1.c_id = '01'
  order by t1.s_score desc
)t1

上面是针对01课程，结果为：

image

第二步：我们将01、02、03课程全部连接起来，通过union实现

表的自连接
SQL实现排序

-- 自己的方法

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   -- SQL实现排序
      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   -- SQL实现排序
      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;

image

-- 参考代码

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

题目20

题目需求

查询学生的总成绩，并进行排名

分析过程

从Score表中查出每个学生的总成绩
连接Student表进行排序查询
如何利用SQL实现排序，参考之前的文章

SQL实现

1、先查询每个学生的总成绩

select 
    s_id
    ,sum(s_score)
from Score
group by s_id
order by 2 desc;

image

将上面的结果和学生信息表进行关联查询：

-- 
select
    s.s_name
    ,s.s_id
    ,t.score
from Student s
join(select 
        s_id
     ,sum(s_score) score
     from Score
     group by s_id
     order by 2 desc
)t
on s.s_id = t.s_id;

image

-- 不使用中间表查询

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;

image

如果想给排名加上一个排序号，参考之前的文章

 -- 加上排序号
 
 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   -- t2和t1相同
  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   -- t1
 order by 3 desc;

image

网友评论

转载部分

本文标题：MySQL经典50题-第16到20题

本文链接：https://www.haomeiwen.com/subject/xlcvcltx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

MySQL经典50题-第16到20题

MySQL50-6-第16-20题

题目16

题目需求

分析过程

SQL实现

题目17

题目需求

分析过程

SQL实现

题目18

题目需求

分析过程

SQL实现

题目19

题目需求

分析过程

SQL实现

题目20

题目需求

分析过程

SQL实现

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

转载部分