MySQL数据库 - 复杂查询（一）

一个不知名大学生，江湖人称菜狗
original author: Jacky Li
Email : 3435673055@qq.com

Time of completion：2024.03.27
Last edited: 2024.03.27

MySQL数据库 - 复杂查询（一）

第1关：交换工资

任务描述

相关知识

编程要求

测试说明

代码如下：

第2关：换座位

任务描述

相关知识

编程要求

测试说明

代码如下：

第3关：分数排名

任务描述

相关知识

编程要求

测试说明

代码如下：

第4关：体育馆的人流量

任务描述

相关知识

编程要求

测试说明

代码如下：

第5关：统计总成绩

任务描述

相关知识

编程要求

测试说明

代码如下：

作者有言

MySQL数据库 - 复杂查询（一）

第1关：交换工资

任务描述

本关任务：给定一张 tb_Salary 表，如下所示，有 m = 男性 和 f = 女性的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。

id	name	sex	salary
1	Elon	f	7000
2	Donny	f	8000
3	Carey	m	6000
4	Karin	f	9000
5	Larisa	m	5500
6	Sora	m	500

要求只使用一句更新update语句，且不允许含有任何select语句完成任务。

编程要求

根据提示并仔细阅读右侧代码，在Begin - End区域内进行代码补充。

提示

可能需要使用到 CASE 函数或 IF 函数，使用方法如下实例：

SELECT
case ###如果
when sex='1' then '男' ###sex='1'，则返回值'男'
when sex='2' then '女' ###sex='2'，则返回值'女'
else '其他' ###其他的返回'其他’
end ###结束
from sys_user ###整体理解：在sys_user表中如果sex='1'，则返回值'男'如果sex='2'，则返回值'女' 否则返回'其他’

select if(sex='1','男','女') as sex from sys_user; ###如果sex='1'则返回值'男' 否则返回值为'女'

测试说明

补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

预期输出：

+----+--------+-----+--------+
| id | name | sex | salary |
+----+--------+-----+--------+
| 1 | Elon | m | 7000 |
| 2 | Donny | m | 8000 |
| 3 | Carey | f | 6000 |
| 4 | karin | m | 9000 |
| 5 | Larisa | f | 5500 |
| 6 | Sora | f | 500 |
+----+--------+-----+--------+

代码如下：

#请在此添加实现代码
########## Begin ##########
-- update tb_Salary set sex = if(sex = 'f','m','f');
update tb_Salary set sex = case when sex= 'm' then'f' else'm' end;
########## End ##########

第2关：换座位

任务描述

本关任务：改变相邻俩学生的座位。

小美是一所中学的信息科技老师，她有一张 tb_Seat座位表，平时用来储存学生名字和与他们相对应的座位 id。

tb_Seat表结构数据如下：

id	name
1	Elon
2	Donny
3	Carey
4	Karin
5	Larisa

现在小美想改变相邻俩学生的座位（若学生人数为奇数，则无需改变最后一位同学的座位），现在需要你编写SQL输出小美想要的的结果。

编程要求

请仔细阅读右侧代码，根据方法内的提示，在Begin - End区域内进行代码补充。

测试说明

补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

预期输出：

id name
1 Donny
2 Elon
3 Karin
4 Carey
5 Larisa

代码如下：

#请在此添加实现代码

########## Begin #########
-- select
--     case 
--     when id%2=0 then id-1 
--     else (case when id=(select max(id) from tb_Seat) then id else id+1 end
--         ) end
--     as id, name
-- from tb_Seat order by id;
SELECT 
CASE 
WHEN MOD(id,2)=1 AND id!=(SELECT MAX(id) FROM tb_Seat) THEN id+1
WHEN MOD(id,2)=0 THEN id-1 
ELSE id 
END 
AS id,name FROM tb_Seat ORDER BY id;

-- SELECT IF(id%2=0,id-1,IF(id=5,id,id+1)) AS id,name 
-- FROM tb_Seat  ORDER BY id;
########## End ##########

第3关：分数排名

任务描述

本关任务：编写SQL查询来实现二种排名方式的分数排名。

score表结构信息如下：

Id	Score
1	3.52
2	3.65
3	4.23
4	3.85
5	4.23
6	3.65

如果两个分数相同，则两个分数排名（Rank）相同。

情况一：平分后的下一个名次是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。例：1、1、2、3、4、4。

情况二：排名是非连续的。例：1、1、1、4、4、6。

编程要求

请仔细阅读右侧代码，根据方法内的提示，在Begin - End区域内进行代码补充。

测试说明

补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

代码如下：

#请在此添加实现代码

########## Begin ##########
select Score,(select count(distinct Score) from score as s2 where s2.Score>=s1.Score) as Rank
from score as s1 order by Score desc;
select Score,(select count(Score)+1 from score as s2 where s2.Score>s1.Score) as Rank
from score as s1 order by Score desc;

########## End ##########

第4关：体育馆的人流量

任务描述

本关任务：某市建了一个新的体育馆，每日人流量信息被记录在gymnasium表中：序号 (id)、日期 (date)、人流量 (visitors_flow)。

请编写一个查询语句，找出人流量处于高峰的记录 id、日期 date 和人流量 visitors_flow，其中高峰定义为前后连续三天人流量均不少于 100。

gymnasium表结构数据如下：

id	date	visitors_flow
1	2019-01-01	58
2	2019-01-02	110
3	2019-01-03	123
4	2019-01-04	67
5	2019-01-05	168
6	2019-01-06	1352
7	2019-01-07	382
8	2019-01-08	326
9	2019-01-09	99

提示：每天只有一行记录，日期随着 id 的增加而增加。

编程要求

请仔细阅读右侧代码，根据方法内的提示，在Begin - End区域内进行代码补充。

测试说明

补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

预期输出：

+----+------------+---------------+
| id | date | visitors_flow |
+----+------------+---------------+
| 5 | 2019-01-05 | 168 |
| 6 | 2019-01-06 | 1352 |
| 7 | 2019-01-07 | 382 |
| 8 | 2019-01-08 | 326 |
+----+------------+---------------+

代码如下：

#请在此添加实现代码

########## Begin ##########
select distinct a.*
from gymnasium a,gymnasium b,gymnasium c
where a.visitors_flow >= 100 and b.visitors_flow >= 100 and c.visitors_flow >= 100 and
      (
       (a.id+1 = b.id and b.id+1 = c.id) or
       (a.id-1 = b.id and a.id+1 = c.id) or
       (a.id-1 = c.id and a.id+1 = b.id) or
       (a.id-2 = b.id and a.id-1 = c.id) or
       (a.id-1 = b.id and a.id-2 = c.id)
      )
order by a.id

########## End ##########

第5关：统计总成绩

任务描述

本关任务：计算每个班的语文总成绩和数学总成绩，其中低于 60 分的成绩不记入总成绩。

tb_score结构数据：

name	chinese	maths
A	89	98
B	99	89
C	55	66
D	88	66
E	55	66
F	88	99

tb_class表结构数据：

stuname	classname
A	C1
B	C2
C	C3
D	C2
E	C1
F	C3

编程要求

请仔细阅读右侧代码，根据方法内的提示，在Begin - End区域内进行代码补充。

测试说明

补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

预期输出：

+-----------+---------+-------+
| classname | chinese | maths |
+-----------+---------+-------+
| C1 | 89 | 164 |
| C2 | 187 | 155 |
| C3 | 88 | 165 |
+-----------+---------+-------+

代码如下：

#请在此添加实现代码
########## Begin ##########
select classname,sum(if(chinese>=60,chinese,0)) chinese,sum(if(maths>=60,maths,0)) maths from tb_score a,tb_class b where a.name=b.stuname group by b.classname;

########## End ##########