MySQL打卡练习-3
练习一：连续出现的数字（难度：中等）
问题描述解题答案 练习二：树节点 （难度：中等）
问题描述解题答案 练习三：至少有五名直接下属的经理 （难度：中等）
问题描述解题答案
练习一：连续出现的数字（难度：中等） 问题描述编写一个 SQL 查询，查找所有至少连续出现三次的数字。
+----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
+-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+解题答案
创建数据
CREATE table nums_arr
(
Id int not null,
Num int
);
TRUNCATE nums_arr;
INSERT INTO nums_arr
VALUES (1, 1),
(2, 1),
(3, 1),
(4, 2),
(5, 1),
(6, 2),
(7, 2),
(10, 2);
代码
# 连续id下的数组重新排序，索引差值相等
# id: 1 2 3 5
# num: 1 1 1 1
# rank:1 2 3 4
# sub: 0 0 0 1
# step1:为连续数字rank
SELECt Id, Num, row_number() over (order by Id) as "new_id"
From nums_arr;
# step2:数字排序
SELECt Id, Num, ROW_NUMBER() over (partition by Num order by Id) as "new_rank"
FROM nums_arr;
# step3:构建新的排序参考
SELECt Id
, Num
, ROW_NUMBER() over (order by Id) as "new_id"
, ROW_NUMBER() over (PARTITION BY Num ORDER BY Id) as "group_new_id"
, ROW_NUMBER() over (order by Id) - ROW_NUMBER() over (PARTITION BY Num ORDER BY Id) as "rank"
FROM nums_arr;
# step4: 获取连续数字
SELECt DISTINCT Num
FROM (SELECt Num, COUNT(1)
From (SELECt Id,
Num,
ROW_NUMBER() over (order by id) - ROW_NUMBER() over (partition by Num order by id) as "rank"
FROM nums_arr) as sub
GROUP BY Num, `rank`
HAVINg count(1) >= 3) as Result
;
练习二：树节点 （难度：中等）
问题描述
对于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 | +----+------+
说明
节点’1’是根节点，因为它的父节点为NULL，有’2’和’3’两个子节点。节点’2’是内部节点，因为它的父节点是’1’，有子节点’4’和’5’。节点’3’，‘4’，'5’是叶子节点，因为它们有父节点但没有子节点。
下面是树的图形：
1 / 2 3 / 4 5
注意
如果一个树只有一个节点，只需要输出根节点属性。
解题答案创建数据
create table tree
(
id int not null,
p_id int
);
insert into tree
values (1, NULL),
(2, 1),
(3, 1),
(4, 2),
(5, 2);
select *
from tree;
代码
# 方法１：CASE WHEN......语句
SELECt id,
CASE
# 判断根节点
WHEN p_id IS NULL THEN "root"
# 取出所有父亲节点
WHEN id IN (select DISTINCT p_id FROM tree) THEN "inner"
# 叶子节点
ELSE "leat"
END AS "type"
FROM tree;
# 方法２：if语句：SELECt IF(判别表达式,'yes','no')
SELECT id,
IF(ISNULL(p_id), "root"
, IF(id IN (SELECT DISTINCT p_id
FROM tree)
, "inner", "leaf")
) AS type
FROM tree;
练习三：至少有五名直接下属的经理 （难度：中等）
问题描述
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 | +-------+
注意:
没有人向自己汇报。
解题答案创建数据
create TABLE employee
(
id int not null,
name varchar(10),
department varchar(10),
manager_id int,
primary key (id)
);
# TRUNCATE TABLE employee;
INSERT INTO employee
VALUES (101, "John", "A", NULL),
(102, "Dan", "A", 101),
(103, "James", "A", 101),
(104, "Amy", "A", 101),
(105, "Ron", "B", 101),
(106, "Anne", "B", 101);
代码
# 方法１
# step1:选出主管id
SELECt manager_id
FROM employee
GROUP BY manager_id
HAVINg count(1) >= 5;
# step2:关联姓名
SELECt name
from employee
where id in (SELECt manager_id
FROM employee
GROUP BY manager_id
HAVINg count(1) >= 5);
# 方法2
# 内连接：INNER JOIN
# JOIN用法链接：https://www.cnblogs.com/fudashi/p/7491039.html
SELECt t2.name
FROM employee t1
INNER JOIN employee as t2
ON t1.manager_id = t2.id
GROUP BY t2.name
HAVINg count(1) >= 5;
