MySQL学习TASK4

项目七：各部门工资最高的员工

创建 Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

Id	Name	Salary	Department Id
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 部门有最高工资。

SELECT 
    d.Name AS Department,
	e.Name AS Employee,
	e.Salary AS Salary
FROM 
    Department d
LEFT JOIN
    Employee e 
ON
    e.DepartmentId=d.Id 
AND 
	e.Salary=(SELECT max(Salary)
		      FROM Employee 
			  WHERE DepartmentId=d.Id)

输出结果如下展示：

项目八: 换座位

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

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

-- 方法一
SELECT
  (
	    CASE 
	        WHEN id%2 = 1 AND id = maxid  THEN id
	        WHEN id%2 = 1 AND id != maxid THEN id+1
	    ELSE 
			    id-1
      END ) AS id, student
 FROM 
     seat, (SELECT max(id) AS maxid FROM seat) AS t
 ORDER BY id;
 --方法二
 select 
    if(id<(select count(*) from seat),
	   if(id%2=0,id-1,id+1),
	   if(id%2=0,id-1,id)) as id,student
from seat
order by id;

结果显示如下：

项目九：分数排名

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

Id	Score
1	3.50
2	3.65
3	4.00
4	3.85
5	4.00
6	3.65

CREATE TABLE scores(
     Id INT,
	 Score FLOAT
);
INSERT INTO scores(Id, Score)
VALUES
     ('1', '3.50'),
	 ('2', '3.65'),
	 ('3', '4.00'),
	 ('4', '3.85'),
	 ('5', '4.00'),
	 ('6', '3.65');
SELECT 
   s1.Score,
   COUNT(DISTINCT s2.Score) AS Rank
FROM
   Scores s1 
INNER JOIN 
   Scores s2 
      ON 
   s1.Score <= s2.Score
GROUP BY s1.Id
ORDER BY s1.Score desc

结果如下：

项目十：行程和用户

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日 期间非禁止用户的取消率，取消率（Cancellation Rate）保留两位小数。

Create table Trips(
         Id int,
		 Client_Id int, 
		 Driver_Id int, 
		 City_Id int, 
		 Status ENUM('completed','cancelled_by_driver','cancelled_by_client'), 
		 Request_at varchar(50)
	);
Create table Users(
         Users_Id int,
		 Banned varchar(50), 
		 Role ENUM('client', 'driver', 'partner')
	);
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');
		 
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');
SELECT
	t.Request_at DAY,
	ROUND(sum(CASE WHEN t.STATUS LIKE 'cancelled%' THEN 1 ELSE 0 END)/ count(t.Id), 2 ) AS 'Cancellation Rate'  
FROM
	Trips t 
INNER JOIN 
	Users u 
	ON 
	(u.Users_Id = t.client_Id AND u.Banned = 'No') 
WHERE
	t.Request_at BETWEEN '2013-10-01' AND '2013-10-03' 
GROUP BY
	t.Request_at;

结果如下：

项目十一：各部门前3高工资的员工

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

TRUNCATE TABLE Employee;
INSERT INTO Employee
VALUES
	( 1, 'Joe', 70000, 1 ),
	( 2, 'Henry', 80000, 2 ),
	( 3, 'San', 60000, 2 ),
	( 4, 'Max', 90000, 1 ),
	( 5, 'Janet', 69000, 1 ),
    ( 6, 'Randy', 85000, 1 );
SELECT 
     d.Name AS Department,
	 e1.Name AS Employee,
	 e1.Salary
FROM Employee e1
   JOIN
	   Department d
   ON
	   e1.DepartmentId = d.Id
WHERE
    (SELECT COUNT(DISTINCT e2.Salary)
	 FROM employee e2
	 WHERE e2.Salary >= e1.Salary AND e2.DepartmentId = e1.DepartmentId) <=3
ORDER BY e1.DepartmentId, e1.salary DESC

结果显示如下：

项目十二：分数排名

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

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

--  方法一
SELECT
	Score,
	rank () over (ORDER BY Score DESC) 'Rank'
FROM
	Scores S
	
-- 方法二
SELECT 
    s.Score,
    (SELECT 
            COUNT(*) + 1
        FROM
            Scores AS s1
        WHERE
            s1.Score > s.Score) AS Rank
FROM
    scores s
ORDER BY Score DESC;

结果如下：

灰常有用的好博客：（开窗函数）https://blog.csdn.net/qq_30505673/article/details/88661037#DENSE_RANK_104