Task06:秋招秘籍 A
练习一: 各部门工资最高的员工(难度:中等)
创建 Employee 表,包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
创建 Department 表,包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询,找出每个部门工资最高的员工。例如,根据上述给定的表格,Max 在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
做法1:先查出整张联结表,然后在where子句中使用 in 添加条件。
第一步:因为部门名在 表 Department 里面,所以需要将表 Employee 和表 Department 联结按起来。
select
d.Name as Department,
e.Name,
e.Salary
from Employee e
inner join Department as d
on e.DepartmentId = d.Id;
+------------+-------+----------+
| Department | Name | Salary |
+------------+-------+----------+
| IT | Joe | 70000.00 |
| Sales | Henry | 80000.00 |
| Sales | Sam | 60000.00 |
| IT | Max | 90000.00 |
+------------+-------+----------+
第二步:因为是每个部门工资最高的员工,而第一步结果太多了。所以需要结果添加筛选条件。
select
d.Name as Department,
e.Name,
e.Salary
from Employee e
inner join Department as d
on e.DepartmentId = d.Id
where (e.DepartmentId, e.Salary)
in (select DepartmentId, max(Salary)
from Employee
group by DepartmentId)
order by e.Salary desc;
+------------+-------+----------+
| Department | Name | Salary |
+------------+-------+----------+
| IT | Max | 90000.00 |
| Sales | Henry | 80000.00 |
+------------+-------+----------+
做法2:先通过子查询将工资最高的员工名及其工资查出来,然后通过内连结将部门名信息加进来。
第1步:通过自联结工资最高的将员工查出来
select e.DepartmentId, e.Name, e.Salary
from Employee e
where (e.DepartmentId, e.Salary)
in(select DepartmentId, max(Salary) as max_sal
from Employee
group by DepartmentId);
+--------------+-------+----------+
| DepartmentId | Name | Salary |
+--------------+-------+----------+
| 2 | Henry | 80000.00 |
| 1 | Max | 90000.00 |
+--------------+-------+----------+
第2步:内连结将部门名信息加进来
select d.Name as Department, t.Name as Employee, t.Salary
from Department d
inner join (
select e.DepartmentId, e.Name, e.Salary
from Employee e
where (e.DepartmentId, e.Salary)
in(select DepartmentId, max(Salary) as max_sal
from Employee
group by DepartmentId)
) as t
on d.Id = t.DepartmentId
order by Salary desc;
+------------+----------+----------+
| Department | Employee | Salary |
+------------+----------+----------+
| IT | Max | 90000.00 |
| Sales | Henry | 80000.00 |
+------------+----------+----------+
练习二: 换座位(难度:中等)
力扣: 626. 换座位
小美是一所中学的信息科技老师,她有一张 seat 座位表,平时用来储存学生名字和与他们相对应的座位 id。其中纵列的id是连续递增的
小美想改变相邻俩学生的座位。你能不能帮她写一个 SQL query 来输出小美想要的结果呢?
请创建如下所示 seat 表:
示例:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如数据输入的是上表,则输出结果如下:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
注意:
如果学生人数是奇数,则不需要改变最后一个同学的座位。
感觉使用 case + mod 。
select
(case
when id = 0 or id = (select count(*) from seat) then id
when id % 2 = 0 and id > 0 then id - 1
else id + 1
end
) as id,
student
from seat
order by id;
+----+---------+
| id | student |
+----+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+----+---------+
# 使用if 等价写法
select
if(id % 2 = 0 and id != 0, id - 1,
if(id = 0 or id = (select count(*) from seat), id, id + 1)
) as id,
student
from seat
order by id;
+----+---------+
| id | student |
+----+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+----+---------+
练习三: 分数排名(难度:中等)
力扣:178. 分数排名
假设在某次期末考试中,二年级四个班的平均成绩分别是 93
、93
、93
、91
,请问可以实现几种排序结果?分别使用了什么函数?排序结果是怎样的?
+-------+-----------+
| class | score_avg |
+-------+-----------+
| 1 | 93 |
| 2 | 93 |
| 3 | 93 |
| 4 | 91 |
+-------+-----------+
可以实现 2 种排序结果:按班级平均成绩升序、按班级平均成绩降序。
# 1. 按班级序号升序
+-------+-----------+
| class | score_avg |
+-------+-----------+
| 1 | 93 |
| 2 | 93 |
| 3 | 93 |
| 4 | 91 |
+-------+-----------+
# 2. 按班级序号降序
+-------+-----------+
| class | score_avg |
+-------+-----------+
| 4 | 91 |
| 3 | 93 |
| 2 | 93 |
| 1 | 93 |
+-------+-----------+
练习四:连续出现的数字(难度:中等)
力扣:180. 连续出现的数字
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如,给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
select Num, count(Num)
from consecutive_num
group by Num;
+------+------------+
| Num | count(Num) |
+------+------------+
| 1 | 4 |
| 2 | 3 |
+------+------------+
练习五:树节点 (难度:中等)
对于tree
表,id
是树节点的标识,p_id
是其父节点的id
。
+----+------+
| id | p_id |
+----+------+
| 1 | null |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
+----+------+
每个节点都是以下三种类型中的一种:
- Root: 如果节点是根节点。
- Leaf: 如果节点是叶子节点。
- Inner: 如果节点既不是根节点也不是叶子节点。
写一条查询语句打印节点id
及对应的节点类型。按照节点id排序。上面例子的对应结果为:
+----+------+
| id | Type |
+----+------+
| 1 | Root |
| 2 | Inner|
| 3 | Leaf |
| 4 | Leaf |
| 5 | Leaf |
+----+------+
select sub.id as s , sup.id as sup
from tree as sub
left join tree as sup
on sub.p_id = sup.id;
+----+------+----+------+
| id | p_id | id | p_id |
+----+------+----+------+
| 1 | null | NULL NULL
| 2 | 1 | 1 | null |
| 3 | 1 | 1 | null |
| 4 | 2 | 2 | 1 |
| 5 | 2 | 2 | 1 |
+----+------+----+------+
练习六:至少有五名直接下属的经理 (难度:中等)
Employee 表包含所有员工及其上级的信息。每位员工都有一个Id,并且还有一个对应主管的Id(ManagerId)。
+------+----------+-----------+----------+
|Id |Name |Department |ManagerId |
+------+----------+-----------+----------+
|101 |John |A |null |
|102 |Dan |A |101 |
|103 |James |A |101 |
|104 |Amy |A |101 |
|105 |Anne |A |101 |
|106 |Ron |B |101 |
+------+----------+-----------+----------+
针对 Employee 表,写一条 SQL 语句找出有 5 个下属的主管。对于上面的表,结果应输出:
+-------+
| Name |
+-------+
| John |
+-------+
注意:
没有人向自己汇报。
select m.Name
from Employee as m
left join Employee as e
on m.Id = e,ManagerId
练习七:查询回答率最高的问题 (难度:中等)
求出 survey_log 表中回答率最高的问题,表格的字段有:uid, action, question_id, answer_id, q_num, timestamp。
uid 是用户 id ;action 的值为:“show”, “answer”, “skip”;当 action 是 “answer” 时,answer_id 不为空,相反,当 action 是 “show” 和 “skip” 时为空(null);q_num 是问题的数字序号。
写一条 sql 语句找出回答率最高的 question_id
。
举例:
输入
uid | action | question_id | answer_id | q_num | timestamp |
---|---|---|---|---|---|
5 | show | 285 | null | 1 | 123 |
5 | answer | 285 | 124124 | 1 | 124 |
5 | show | 369 | null | 2 | 125 |
5 | skip | 369 | null | 2 | 126 |
输出
question_id |
---|
285 |
说明:问题 285 的回答率为 1/1 ,然而问题 369 的回答率是 0/1 ,所以输出是 285 。
**注意:**最高回答率的意思是:同一个问题出现的次数中回答的比例。
练习八:各部门前3高工资的员工(难度:中等)
将练习一中的 employee
表清空,重新插入以下数据(也可以复制练习一中的 employee
表,再插入第5、第6行数据):
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
+----+-------+--------+--------------+
编写一个 SQL 查询,找出每个部门工资前三高的员工。例如,根据上述给定的表格,查询结果应返回:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
此外,请考虑实现各部门前N高工资的员工功能。
# 找出每个部门工资前三高的员工
select d.Name, e.Name as Employee, e.Salary
from Department as d
inner join employee as e
on d.Id = e.DepartmentId
limit 0, 3
order by Salary desc;
练习九:平面上最近距离 (难度: 困难)
point_2d 表包含一个平面内一些点(超过两个)的坐标值(x,y)。
写一条查询语句求出这些点中的最短距离并保留2位小数。
|x | y |
|----|----|
| -1 | -1 |
| 0 | 0 |
| -1 | -2 |
最短距离是1,从点(-1,-1)到点(-1,-2)。所以输出结果为:
+--------+
|shortest|
+--------+
|1.00 |
+--------+
注意: 所有点的最大距离小于10000。
练习十:行程和用户(难度:困难)
Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id,Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型,枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
Id | Client_Id | Driver_Id | City_Id | Status | Request_at |
---|---|---|---|---|---|
1 | 1 | 10 | 1 | completed | 2013-10-1 |
2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-1 |
3 | 3 | 12 | 6 | completed | 2013-10-1 |
4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-1 |
5 | 1 | 10 | 1 | completed | 2013-10-2 |
6 | 2 | 11 | 6 | completed | 2013-10-2 |
7 | 3 | 12 | 6 | completed | 2013-10-2 |
8 | 2 | 12 | 12 | completed | 2013-10-3 |
9 | 3 | 10 | 12 | completed | 2013-10-3 |
10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-3 |
Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止,Role 则是一个表示(‘client’, ‘driver’, ‘partner’)的枚举类型。
+----------+--------+--------+
| Users_Id | Banned | Role |
+----------+--------+--------+
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
+----------+--------+--------+
写一段 SQL 语句查出2013年10月1日至2013年10月3日期间非禁止用户的取消率。基于上表,你的 SQL 语句应返回如下结果,取消率(Cancellation Rate)保留两位小数。
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
Task06:秋招秘籍 B
练习一:行转列
假设 A B C 三位小朋友期末考试成绩如下所示:
+-----+-----------+------|
| name| subject |score |
+-----+-----------+------|
| A | chinese | 99 |
| A | math | 98 |
| A | english | 97 |
| B | chinese | 92 |
| B | math | 91 |
| B | english | 90 |
| C | chinese | 88 |
| C | math | 87 |
| C | english | 86 |
+-----+-----------+------|
请使用 SQL 代码将以上成绩转换为如下格式:
+-----+-----------+------|---------|
| name| chinese | math | english |
+-----+-----------+------|---------|
| A | 99 | 98 | 97 |
| B | 92 | 91 | 90 |
| C | 88 | 87 | 86 |
+-----+-----------+------|---------|
解
select
sum(case when subject = "chinese" then score else null end) chinese,
sum(case when subject = "math" then score else null end) math,
sum(case when subject = "english" then score else null end) english
from tbl_score
group by name;
练习二:列转行
假设 A B C 三位小朋友期末考试成绩如下所示:
+-----+-----------+------|---------|
| name| chinese | math | english |
+-----+-----------+------|---------|
| A | 99 | 98 | 97 |
| B | 92 | 91 | 90 |
| C | 88 | 87 | 86 |
+-----+-----------+------|---------|
请使用 SQL 代码将以上成绩转换为如下格式:
+-----+-----------+------|
| name| subject |score |
+-----+-----------+------|
| A | chinese | 99 |
| A | math | 98 |
| A | english | 97 |
| B | chinese | 92 |
| B | math | 91 |
| B | english | 90 |
| C | chinese | 88 |
| C | math | 87 |
| C | english | 86 |
+-----+-----------+------|
解
select
sum(case when subject = "chinese" then subject else null end) chinese,
sum(case when subject = "math" then subject else null end) math,
sum(case when subject = "english" then subject else null end) english
from tbl_score
group by name;
练习三:带货主播
假设,某平台2021年主播带货销售额日统计数据如下:
表名 anchor_sales
+-------------+------------+---------|
| anchor_name | date | sales |
+-------------+------------+---------|
| A | 20210101 | 40000 |
| B | 20210101 | 80000 |
| A | 20210102 | 10000 |
| C | 20210102 | 90000 |
| A | 20210103 | 7500 |
| C | 20210103 | 80000 |
+-------------+------------+---------|
定义:如果某主播的某日销售额占比达到该平台当日销售总额的 90% 及以上,则称该主播为明星主播,当天也称为明星主播日。
请使用 SQL 完成如下计算:
a. 2021年有多少个明星主播日?
b. 2021年有多少个明星主播?
练习四:MySQL 中如何查看sql语句的执行计划?可以看到哪些信息?
练习五:解释一下 SQL 数据库中 ACID 是指什么
A C I D 表示原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)。
-
原子性
MySQL通过事务来保证原子性。一个innodb事务被MySQL视为最小单元,出现错误可以事务回滚。
-
一致性
MySQL通过 InnoDB doublewrite buffer 和 InnoDB crash recovery。
-
隔离性
MySQL通过个事务的隔离级别保证隔离性。
-
持久性
MySQL通过双innodb写缓冲区、备份策略等,保证持久性。
Task06:秋招秘籍 C
练习一:行转列
假设有如下比赛结果
+--------------+-----------+
| cdate | result |
+--------------+-----------+
| 2021-01-01 | 胜 |
| 2021-01-01 | 负 |
| 2021-01-03 | 胜 |
| 2021-01-03 | 负 |
| 2021-01-01 | 胜 |
| 2021-01-03 | 负 |
+------------+-----------+
请使用 SQL 将比赛结果转换为如下形式:
+--------------+-----+-----|
| 比赛日期 | 胜 | 负 |
+--------------+-----------+
| 2021-01-01 | 2 | 1 |
| 2021-01-03 | 1 | 2 |
+------------+-----------+
解
select
sum(case when resut = "胜" then count(result) else null end) as 胜,
sum(case when resut = "负" then count(result) else null end) as 负,
from tbl_race
group by cdate;
练习二:列转行
假设有如下比赛结果
+--------------+-----+-----|
| 比赛日期 | 胜 | 负 |
+--------------+-----------+
| 2021-01-01 | 2 | 1 |
| 2021-01-03 | 1 | 2 |
+------------+-----------+
请使用 SQL 将比赛结果转换为如下形式:
+--------------+-----------+
| cdate | result |
+--------------+-----------+
| 2021-01-01 | 胜 |
| 2021-01-01 | 负 |
| 2021-01-03 | 胜 |
| 2021-01-03 | 负 |
| 2021-01-01 | 胜 |
| 2021-01-03 | 负 |
+------------+-----------+
解
select
sum(case when resut = "胜" then result else null end) as 胜,
sum(case when resut = "负" then result else null end) as 负,
from tbl_race
group by cdate;
练习三:连续登录
有用户表行为记录表t_act_records表,包含两个字段:uid(用户ID),imp_date(日期)
- 计算2021年每个月,每个用户连续登录的最多天数
- 计算2021年每个月,连续2天都有登录的用户名单
- 计算2021年每个月,连续5天都有登录的用户数
构造表mysql如下:
DROP TABLE if EXISTS t_act_records;
CREATE TABLE t_act_records
(uid VARCHAR(20),
imp_date DATE);
INSERT INTO t_act_records VALUES('u1001', 20210101);
INSERT INTO t_act_records VALUES('u1002', 20210101);
INSERT INTO t_act_records VALUES('u1003', 20210101);
INSERT INTO t_act_records VALUES('u1003', 20210102);
INSERT INTO t_act_records VALUES('u1004', 20210101);
INSERT INTO t_act_records VALUES('u1004', 20210102);
INSERT INTO t_act_records VALUES('u1004', 20210103);
INSERT INTO t_act_records VALUES('u1004', 20210104);
INSERT INTO t_act_records VALUES('u1004', 20210105);