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 函数，使用方法如下实例：

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

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

三、预期输出：

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

 四、代码

########## Begin ##########

update tb_Salary set sex = if(sex = 'f','m','f') where id in(1,2,3,4,5,6);
########## End ##########

第2关 换座位

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

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

tb_Seat表结构数据如下：

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

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

二、编程要求

根据提示，在Begin - End区域内进行代码补充。

三、预期输出：

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

 四、代码

########## Begin ##########
select
case id
when 1 then 2
when 2 then 1
when 3 then 4
when 4 then 3
when 5 then 5
else 0
end id,name from tb_Seat order by id asc;

########## 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 WHERE score>=s.score) AS Rank FROM score s ORDER BY score DESC;
SELECT Score,(SELECT COUNT(Score)+1 FROM score WHERE score>s.score) AS Rank FROM score s 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区域内进行代码补充。

三、预期输出：

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

 四、代码

########## 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区域内进行代码补充。

三、预期输出：

1. +-----------+---------+-------+
2. | classname | chinese | maths |
3. +-----------+---------+-------+
4. | C1     | 89    | 164  |
5. | C2     | 187   | 155  |
6. | C3     | 88    | 165  |
7. +-----------+---------+-------+

 四、代码

########## Begin ##########
select c.classname,
sum(case when chinese> 60 then chinese else 0 End) chinese,
sum(case when maths > 60 then maths else 0 end) maths
from tb_score s, tb_class c
where c.stuname=s.name
group by classname;

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