MySQL 基础 3

项目十: 各部门工资最高的员工（难度：中等）

创建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 |

±-----------±---------±-------+

创建表：
DROP TABLE IF EXISTS employee;
DROP TABLE IF EXISTS department;

CREATE TABLE 'employee' (
  'id' int(11) NOT NULL AUTO_INCREMENT COMMENT 'id唯一标识 /注：自增',
  'name' varchar(50) DEFAULT NULL COMMENT '名称',
  'salary' int(11) DEFAULT NULL COMMENT '薪水',
  'departmentId' varchar(50) DEFAULT NULL COMMENT 'id',
  PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表';

CREATE TABLE 'department' (
 'id' int(11) NOT NULL AUTO_INCREMENT COMMENT 'id唯一标识 /注：自增',
  'name' varchar(50) DEFAULT NULL COMMENT '部门名称',
  PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表';

插入数据：
INSERT INTO employee (id,name,salary,departmentId) VALUES(1,'Joe',70000,'1');
INSERT INTO employee (id,name,salary,departmentId)VALUES(2,'Henry',80000,'2');
INSERT INTO employee (id,name,salary,departmentId) VALUES(3,'sam',60000,'2');
INSERT INTO employee (id,name,salary,departmentId) VALUES(4,'max',90000,'1');
INSERT INTO department (id,name) VALUES(1,'IT');
INSERT INTO department (id,name) VALUES(2,'Sales');

选取：
SELECT d.Name as department,e.Name as employee,e.salary
FROM department d,employee e
WHERE e.departmentId=d.id and e.salary=(Select max(salary) from employee where departmentId=d.id);

结果如下：

参考：
https://blog.csdn.net/tangerine_/article/details/86000226

项目十一: 换座位（难度：中等）

小美是一所中学的信息科技老师，她有一张 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 |

±--------±--------+

注意：

如果学生人数是奇数，则不需要改变最后一个同学的座位。

创建表：
CREATE TABLE seat(id INT,Student VARCHAR(10));
插入数据：
INSERT INTO seat VALUES(1,'Abbot');
INSERT INTO seat VALUES(2,'Doris');
INSERT INTO seat VALUES(3,'Emerson');
INSERT INTO seat VALUES(4,'Green');
INSERT INTO seat VALUES(5,'Jeames');

选取：
SELECT(    CASE
    WHEN id %2 = 1 AND id!=max_id THEN id+1
    WHEN id %2 = 0 THEN id-1
    WHEN id = max_id THEN id
    END) AS id,student 
    FROM(SELECT id, student, (SELECT MAX(id) FROM seat) AS max_id FROM seat) a
    ORDER BY id;

结果：

参考：https://blog.csdn.net/weixin_34209851/article/details/89535760

项目十二: 分数排名（难度：中等）

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

创建以下score表：

±—±------+

| Id | Score |

±—±------+

| 1 | 3.50 |

| 2 | 3.65 |

| 3 | 4.00 |

| 4 | 3.85 |

| 5 | 4.00 |

| 6 | 3.65 |

±—±------+

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

±------±-----+

| Score | Rank |

±------±-----+

| 4.00 | 1 |

| 4.00 | 1 |

| 3.85 | 2 |

| 3.65 | 3 |

| 3.65 | 3 |

| 3.50 | 4 |

±------±-----+

创建表：
CREATE TABLE Scores (Id INT,Score DOUBLE);

插入数据：
INSERT INTO Scores VALUES(1,3.50)
INSERT INTO Scores VALUES(2,3.65)
INSERT INTO Scores VALUES(3,4.00);
INSERT INTO Scores VALUES(4,3.85);
INSERT INTO Scores VALUES (5,4.00);
INSERT INTO Scores VALUES(6,3.65);
            
选取：
SELECT u.score,a.rank AS Rank FROM Scores u,
(SELECT @counter:=@counter+1 AS rank,t.score FROM (SELECT @counter:=0,score FROM Scores GROUP BY score ORDER BY score DESC)AS t)a
WHERE u.score=a.score ORDER BY Rank ASC;

项目十三：连续出现的数字（难度：中等）

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

±—±----+

| Id | Num |

±—±----+

| 1 | 1 |

| 2 | 1 |

| 3 | 1 |

| 4 | 2 |

| 5 | 1 |

| 6 | 2 |

| 7 | 2 |

±—±----+

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

±----------------+

| ConsecutiveNums |

±----------------+

| 1 |

±----------------+

创建表：
Create table If Not Exists Logs (Id int,Num int);
Truncate table Logs;

插入数据：
INSERT INTO Logs (Id, Num) VALUES('1','1');
INSERT INTO Logs (Id, Num)VALUES('2','1');
INSERT INTO Logs (Id, Num)VALUES('3', '1');
INSERT INTO Logs (Id, Num)VALUES('4','2');
INSERT INTO Logs (Id, Num) VALUES('5','1');
INSERT INTO Logs (Id, Num) VALUES('6','2');
INSERT INTO Logs (Id, Num) VALUES('7','2');

选取：
SELECT DISTINCT l1.Num as ConsecutiveNums
FROM Logs l1, Logs l2, Logs l3
WHERE (l1.id = l2.id - 1
AND l2.id = l3.id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num);

结果：

项目十四：树节点 （难度：中等）

对于 tree 表，id 是树节点的标识，p_id 是其父节点的 id。

±—±-----+

| id | p_id |

±—±-----+

| 1 | null |

| 2 | 1 |

| 3 | 1 |

| 4 | 2 |

| 5 | 2 |

±—±-----+

每个节点都是以下三种类型中的一种：

Leaf: 如果节点是根节点。
Root: 如果节点是叶子节点。
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

注意

如果一个树只有一个节点，只需要输出根节点属性。

drop table if exists tree;

创建表：
CREATE TABLE tree(
id int,
p_id int);

插入数据：
INSERT INTO tree VALUES(1,NULL);
INSERT INTO tree VALUES(2,1);
INSERT INTO tree VALUES(3,1);
INSERT INTO tree VALUES(4,2);
INSERT INTO tree VALUES(5,2);

选取：
SELECT id,(CASE
					WHEN (select count(*) from tree) = 1 then 'Root'
					WHEN id not in (select DISTINCT t1.id
						FROM tree as t1,tree as t2
						where t1.id = t2.p_id) then 'Leaf'
					WHEN P_id is NULL then 'Root'
					ELSE 'Inner'
					END)
					as Type
FROM tree;

结果：

项目十五：至少有五名直接下属的经理 （难度：中等）

Employee 表包含所有员工及其上级的信息。每位员工都有一个Id，并且还有一个对应主管的Id（ManagerId）。

1

2

3

4

5

6

7

8

9

10

±-----±---------±----------±---------+

|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个下属的主管。对于上面的表，结果应输出：

1

2

3

4

5

±------+

| Name |

±------+

| John |

±------+

注意:

没有人向自己汇报。

drop table if exists Employee;
创建表：
CREATE TABLE Employee(
Id INT PRIMARY KEY,
Name VARCHAR (255),
Department VARCHAR (255),
ManagerId INT
);

插入数据：
INSERT INTO Employee VALUES('101', 'John', 'A', 'null');
INSERT INTO Employee VALUES('102', 'Dan', 'A', '101 ');
INSERT INTO Employee VALUES('103', 'James', 'A', '101');
INSERT INTO Employee VALUES('104', 'Amy', 'A', '101');
INSERT INTO Employee VALUES('105', 'Anne', 'A', '101');
INSERT INTO Employee VALUES('106', 'Ron', 'B', '101');

选取：
SELECT Name
FROM (
SELECT ManagerId, COUNT(Id) AS lalala
FROM Employee
GROUP BY ManagerId
) m, Employee e
WHERE m.ManagerId = e.Id
AND lalala >= 5;

结果：