MySQL50题_第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

题目需求

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

分析过程

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

课程名	分数	排名
英语	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课程，结果为：

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