mysql第四次打卡

MySQL 实战 - 复杂项目
项目十：写一段 SQL 语句查出 **2013年10月1日 **至 **2013年10月3日 **期间非禁止用户的取消率。

创建trips表和users表如下

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

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

用select的嵌套进行查询

SELECT t.request_at,round(count(status != 'completed' OR NULL)/count(*),2) AS `Cancellation Rate` 
FROM
(SELECT request_at, `status` 
FROM trips
LEFT JOIN users ON   trips.Client_Id = users.Users_Id 
WHERE banned = 'no' AND  (request_at BETWEEN '2013-10-01' AND '2013-10-04'))t
GROUP BY t.request_at

结果如下：

request_at  Cancellation Rate
2013-10-01	0.33
2013-10-02	0.00
2013-10-03	0.50

项目十一：编写一个 SQL 查询，找出每个部门工资前三高的员工
清空工资表，并重新插入数据如表所示：

id name salary department_id
1	Joe	70000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1
5	Janet	69000	1
6	Randy	85000	1

select d.Name Department, e.Name Employee, e.Salary Salary 
from Employee e inner join Department d 
on e.department_id = d.Id
where (
	select count(distinct c.Salary) 
	from Employee c
	where e.Salary < c.Salary and e.department_id = c.department_id
) < 3
order by e.department_id, e.Salary desc;

输出结果：

department employee salary
IT	Max	90000
IT	Randy	85000
IT	Joe	70000
Sales	Henry	80000
Sales	Sam	60000

项目十二 分数排名 - （难度：中等） 依然是昨天的分数表，实现排名功能，但是排名是非连续的

SELECT t.score,t.rank
FROM score
LEFT JOIN
(SELECT rank() over(ORDER BY score DESC) as `rank`,score,id
FROM score
GROUP BY score,id)t
ON t.id=  score.id
ORDER BY t.rank 

输出结果：

score rank
4	1
4	1
3.85	3
3.65	4
3.65	4
3.5	6

这次的按非连续排名，与上次的任务相比，只是两个边的连接条件发生了变化，要注意区分。