MySQL打卡练习-3

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;