MySQL练习(换座位、排序、取消订单率、部门Top3薪资）

一、换座位

要求：改变相邻两学生的座位(id)，如果学生人数是奇数，则不需要改变最后一个同学的座位。 创建表，并插入数据

#创建表，设定ID为自增 CREATE TABLE seats( Id INT PRIMARY key auto_increment, student VARCHAR(20) ); #插入数据 INSERT INTO seats (student) VALUES('Abbot'),('Doris'),('Emerson'),('Green'),('Jeames'); #查看当前表结果 SELECT * FROM seats;

解析： 奇偶对换，并且当座位数是奇数时，最后以为同学不调整座位； 当不是最大座位id时，进行座位id的前后调整，从1变成2，2变成1，逐次进行。 对应判断内容： id的奇偶性 与 最大id SELECT *, (CASE WHEN MOD(id,2) = 1 AND id = (SELECT MAX(ID) FROM seats) THEN id WHEN MOD(id,2)=1 THEN id + 1 ELSE id-1 END) as id_new FROM seats ORDER BY id_new; 或者从偶数id进行判断 SELECT *, (CASE WHEN MOD(id,2) = 1 AND id = (SELECT MAX(ID) FROM seats) THEN id WHEN MOD(id,2)=0 THEN id -1 #先判断偶数座位，id减少一位 ELSE id +1 END) as id_new FROM seats ORDER BY id_new

二、分数排名 - 连续排名

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

#创建表 CREATE TABLE scores( ID INT PRIMARY KEY AUTO_INCREMENT, Score float(5,2) NOT NULL); #插入数据 INSERT INTO scores(score) VALUES(3.50),(3.65),(4.00),(3.85),(4.00),(3.65); #查看当前表结果 SELECT * FROM scores

排名：数据大小对比的过程，如果没有大于当前值的，则排名第1，有1个大于当前值的，当前值排名第2.. 自连接，并且为非等值连接 SELECT s1.Score, COUNT(DISTINCT s2.score) as score_rank FROM scores s1 JOIN scores s2 ON s1.score <= s2.score GROUP BY s1.id ORDER BY s1.score DESC;

三、分数排名 - 非连续排名

#非连续排名，依旧为自连接结合子查询 SELECT score, (SELECT COUNT(score) FROM scores s2 WHERE s2.score>s1.score) + 1 as rank from scores s1 order by rank

四、行程和用户

Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。 创建表并插入数据

#创建用户表 CREATE TABLE users( users_id INT NOT NULL PRIMARY KEY, banned VARCHAR(3) NOT NULL, 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'); #创建trip表 CREATE TABLE Trips ( Id INT NOT NULL PRIMARY KEY, Client_Id int NOT NULL, Driver_Id int not null, City_Id int not null, Status ENUM('completed', 'cancelled_by_driver','cancelled_by_client'), Request_at date, foreign key(Client_Id) references Users(Users_Id), foreign key(Driver_Id) references Users(Users_Id) ); #插入数据 insert into Trips values(1,1,10,1,'completed','2013-10-01'); insert into Trips values(2,2,11,1,'cancelled_by_driver','2013-10-01'); insert into Trips values(3,3,12,6,'completed','2013-10-01'); insert into Trips values(4,4,13,6,'cancelled_by_client','2013-10-01'); insert into Trips values(5,1,10,1,'completed','2013-10-02'); insert into Trips values(6,2,11,6,'completed','2013-10-02'); insert into Trips values(7,3,12,6,'completed','2013-10-02'); insert into Trips values(8,2,12,12,'completed','2013-10-03'); insert into Trips values(9,3,10,12,'completed','2013-10-03'); insert into Trips values(10,4,13,12,'cancelled_by_driver','2013-10-03'); #查看两个表的数据 SELECT * FROM users; SELECT * FROM trips;

写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

非禁止用户取消率，非禁止用户：用户banned 不等于YES 查看用户数据，需要将user表与trip表中客户数据与userid关联 用户取消率 = 取消订单数量 / 总计订单数量 # step1 查看取消订单数量 SELECT Request_at day1, COUNT(*) as cancelnum #取消订单的数量 FROM trips t LEFT JOIN users u ON t.client_id = u.users_id WHERE u.banned != 'Yes' #非禁止用户 AND t.status != 'completed' #取消订单，包含司机取消和客户取消 AND Request_at >='2013-10-01' AND Request_at <= '2013-10-03' #限定日期 GROUP BY day1; #按照日期进行分组

# step2 查看总计订单数量 SELECT Request_at day2, COUNT(*) as totalnum FROM trips t LEFT JOIN users u ON t.client_id = u.users_id WHERE u.banned != 'Yes' #对比1，删除了订单状态的条件，查看所有订单 AND Request_at >='2013-10-01' AND Request_at <= '2013-10-03' GROUP BY day2;

#step3 将结果1和结果2合并，并计算比率 = ROUND( cancelnum/totalnum,2) #需要考虑没有取消订单的10/2的情况，返回0即可 SELECT day2, IFNULL(ROUND(cancelnum/totalnum,2),0) as "Cancellation Rate" FROM (SELECT Request_at day1, COUNT(*) as cancelnum FROM trips t LEFT JOIN users u ON t.client_id = u.users_id WHERE u.banned != 'Yes' AND t.status != 'completed' AND Request_at >='2013-10-01' AND Request_at <= '2013-10-03' GROUP BY day1 ) as tc #作为cancel table RIGHT JOIN (SELECT Request_at day2, COUNT(*) as totalnum FROM trips t LEFT JOIN users u ON t.client_id = u.users_id WHERE u.banned != 'Yes' AND Request_at >='2013-10-01' AND Request_at <= '2013-10-03' GROUP BY day2) as tt #作为total table ON tc.day1=tt.day2;

五、各部门工资最高的员工

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。 创建表并插入数据

CREATE TABLE Employee ( `Id` INT ( 4 ) NOT NULL AUTO_INCREMENT, `Name` VARCHAR ( 20 ) NOT NULL, `Salary` INT ( 10 ) NOT NULL, `DepartmentId` INT ( 4 ) NOT NULL, PRIMARY KEY ( `Id` ) ); INSERT INTO employee ( name, salary, departmentid ) VALUES ( 'Joe', 70000, 1 ), ( 'Henry', 80000, 2 ), ( 'Sam', 60000, 2 ), ( 'Max', 90000, 1 ) CREATE TABLE Department ( `Id` INT ( 4 ) AUTO_INCREMENT, `Name` VARCHAR ( 20 ), PRIMARY KEY ( `Id` ) ); INSERT INTO Department ( NAME ) VALUES ( 'IT' ), ( 'Sales' ); #员工表与部门表连接，同时需要查看每个部门最大薪资是哪个员工的 SELECT d.name department, e.name employee, e.salary FROM employee e JOIN department d ON e.departmentid = d.id JOIN (select departmentid, MAX(salary) ms FROM employee GROUP BY departmentid) dm ON e.departmentid = dm.departmentid AND e.salary = dm.ms GROUP BY d.id;

六、各部门薪资前3高的员工

编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回： 再插入以下数据

INSERT INTO employee ( name, salary, departmentid ) VALUES ( 'Janet', 69000, 1 ), ( 'Randy', 85000, 1 );

SELECT d.name AS Department, e.Name AS Employee, Salary FROM Employee e JOIN Department d ON e.DepartmentId = d.Id WHERE (SELECT COUNT(DISTINCT em.Salary) FROM Employee em WHERE em.Salary >= e.Salary AND em.DepartmentId = e.DepartmentId) <= 3 GROUP BY Department , Salary DESC;