MySQL经典50题：第16到20题

公众号：尤而小屋
作者：Peter
编辑：Peter

大家好，我是Peter~

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

1. 自连接
2. SQL实现排序
3. 多表查询

题目是：

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

题目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;

复制

然后直接将上面的结果和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
);

复制

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;

复制

-- 自己的方法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

复制

题目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;

复制

-- 自己的方法
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个字段

复制

-- 参考方法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;

复制

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;

复制

题目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;

复制

题目19

题目需求

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

分析过程

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

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课程，结果为：

第二步：我们将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;

复制

-- 参考代码

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

题目需求

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

分析过程

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

SQL实现

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

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

复制

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

-- 
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;

复制

-- 不使用中间表查询

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;

复制

如果想给排名加上一个排序号，参考如下代码：

-- 加上排序号

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;

复制