MySQL学习记录（六）—— 复杂项目实战

项目十：行程和用户（难度：困难）

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-01
2	2	11	1	cancelled_by_driver	2013-10-01
3	3	12	6	completed	2013-10-01
4	4	13	6	cancelled_by_client	2013-10-01
5	1	10	1	completed	2013-10-02
6	2	11	6	completed	2013-10-02
7	3	12	6	completed	2013-10-02
8	2	12	12	completed	2013-10-03
9	3	10	12	completed	2013-10-03
10	4	13	12	cancelled_by_driver	2013-10-03

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

-- 创建表 Trips 并插入数据
Create table If Not Exists Trips 
(
    Id INT(5),
    Client_Id INT(5),
    Driver_Id INT(5), 
    City_Id INT(5), 
    Status ENUM('completed','cancelled_by_driver', 'cancelled_by_client'),
    Request_at varchar(50)
);
INSERT INTO Trips(Id, Client_Id, Driver_Id,City_Id, Status, Request_at)  
           VALUES
                 ('1', '1', '10', '1', 'completed', '2013-10-01'),
                 ('2', '2', '11', '1', 'cancelled_by_driver', '2013-10-01'),
                 ('3', '3', '12', '6', 'completed', '2013-10-01'),
                 ('4', '4', '13', '6','cancelled_by_client', '2013-10-01'),
                 ('5', '1', '10', '1', 'completed', '2013-10-02'),
                 ('6', '2', '11', '6', 'completed', '2013-10-02'),
                 ('7', '3', '12', '6', 'completed', '2013-10-02'),
                 ('8', '2', '12', '12', 'completed', '2013-10-03'),
                 ('9', '3', '10', '12', 'completed', '2013-10-03'),
                 ('10', '4', '13', '12','cancelled_by_driver', '2013-10-03');
-- 创建表 Users 并插入数据
Create table If Not Exists Users 
(
    Users_Id INT(5),
    Banned VARCHAR(50), 
    Role ENUM('client', 'driver', 'partner')
);
INSERT INTO Users (Users_Id, Banned, Role)
            VALUES 
                  ('1', 'No', 'client'),
                  ('2', 'Yes', 'client'),
                  ('3', 'No', 'client'),
                  ('4', 'No', 'client'),
                  ('10', 'No', 'driver'),
                  ('11', 'No', 'driver'),
                  ('12', 'No', 'driver'),
                  ('13', 'No', 'driver');
-- 查询 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率
SELECT request_at,
       ROUND(SUM(CASE WHEN Status="completed" THEN 0 ELSE 1 END)/COUNT(*),2) 
       AS Cancellation_Rate
    FROM 
    (
      SELECT * FROM Trips 
          WHERE client_id NOT IN
          (
            SELECT users_id FROM Users 
            WHERE banned = "yes" and role = "client"
           ) 
          AND request_at >= "2013-10-01" 
          AND request_at <= "2013-10-03"
    ) AS t
    GROUP BY request_at 
    ORDER BY request_at ASC;

项目十一：各部门前3高工资的员工（难度：中等）

将项目七中的 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高工资的员工功能。

-- 删除之前创建的 Employee 表的数据，重新插入数据
TRUNCATE TABLE Employee;
INSERT INTO Employee(Id, Name, Salary, DepartmentId)
              VALUES
                    (1, "Joe", 70000, 1),
                    (2, "Henry", 80000, 2),
                    (3, "Sam", 60000, 2),
                    (4, "Max", 90000, 1),
                    (5, "Janet", 69000, 1),
                    (6, "Randy", 85000, 1);
-- 将两个表按照部门编号进行内联结保存为新表 h ，再根据部门分组找出前三高的工资
CREATE TABLE h AS
    SELECT d.Name Department, e.Name Employee, e.Salary 
        FROM Employee e INNER JOIN Department d
            ON e.DepartmentId = d.Id;
SELECT Department, h1.Employee, h1.Salary
    FROM h h1
    WHERE 3 >   
           (
            SELECT COUNT(DISTINCT h2.Salary) 
            FROM h h2
            WHERE h2.Salary > h1.Salary AND h1.Department = h2.Department
            )
    ORDER BY Department, h1.Salary DESC;

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

依然是项目九的分数表，实现排名功能，但是排名是非连续的，如下：

Score	Rank
4.00	1
4.00	1
3.85	3
3.65	4
3.65	4
3.50	6

-- 创建score表并插入数据
CREATE TABLE score
(
	Id INT(10) PRIMARY KEY,
	Score FLOAT(4)
);
INSERT INTO score(Id, Score)
           VALUES(1, 3.50),
                 (2, 3.65),
                 (3, 4.00),
                 (4, 3.85),
                 (5, 4.00),
                 (6, 3.65);
```sql
-- 创建score表并插入数据
CREATE TABLE score
(
	Id INT(10) PRIMARY KEY,
	Score FLOAT(4)
);
INSERT INTO score(Id, Score)
           VALUES(1, 3.50),
                 (2, 3.65),
                 (3, 4.00),
                 (4, 3.85),
                 (5, 4.00),
                 (6, 3.65);
-- 用左联结，条件是左表的分数小于右表的分数，根据id分组后，统计右表分数的个数并+1
SELECT s1.Score, COUNT(s2.Score) + 1 Rank
    FROM Score s1 LEFT JOIN Score s2
        ON s1.Score < s2.Score
    GROUP BY s1.Id
    ORDER BY s1.Score DESC;