MySQL 基础 4
首先放张图，以便于自己日后查看复习，该图来自如下链接：
https://blog.youkuaiyun.com/y3329223/article/details/88084323
项目十六 分数排名 （难度：中等）
依然是昨天的分数表，实现排名功能，但是排名需要是非连续的，如下：
±------±-----+
| Score | Rank |
±------±-----+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 3 |
| 3.65 | 4 |
| 3.65 | 4 |
| 3.50 | 6 |
±------±-----
DROP TABLE IF EXISTS Score;
CREATE TABLE Score(
Id INT,
score DECIMAL(3,2)
);
INSERT INTO Score VALUES(1,3.65);
INSERT INTO Score VALUES(2,3.50);
INSERT INTO Score VALUES(3,4.00);
INSERT INTO Score VALUES(4,3.85);
INSERT INTO Score VALUES(5,3.65);
INSERT INTO Score VALUES(6,4.00);
SELECT score,
CASE
WHEN @pre_score = score THEN @rank_tmp
WHEN @pre_score:=score THEN @rank_tmp:=@rank_tmp+1
END AS 'rank'
FROM score,(SELECT @rank_tmp:=0,@pre_score:=NULL) tmp
ORDER BY
score.score DESC;
结果如下;
参考：
https://www.cnblogs.com/sunchaothu/p/10467361.html
项目十七：查询回答率最高的问题 （难度：中等）
求出survey_log表中回答率最高的问题，表格的字段有：uid, action, question_id, answer_id, q_num, timestamp。
uid是用户id；action的值为：“show”， “answer”， “skip”；当action是"answer"时，answer_id不为空，相反，当action是"show"和"skip"时为空（null）；q_num是问题的数字序号。
写一条sql语句找出回答率最高的问题。
举例：
输入
| 12345678 | ±-----±----------±-------------±-----------±----------±-----------+| uid | action | question_id | answer_id | q_num | timestamp |±-----±----------±-------------±-----------±----------±-----------+| 5 | show | 285 | null | 1 | 123 || 5 | answer | 285 | 124124 | 1 | 124 || 5 | show | 369 | null | 2 | 125 || 5 | skip | 369 | null | 2 | 126 |±-----±----------±-------------±-----------±----------±-----------+ |
|---|
输出
| 12345 | ±------------+| survey_log |±------------+| 285 |±------------+ |
|---|
说明
问题285的回答率为1/1，然而问题369的回答率是0/1，所以输出是285。
注意： 最高回答率的意思是：同一个问题出现的次数中回答的比例。
创建表：
DROP TABLE IF EXISTS survey_log;
CREATE TABLE survey_log(
uid INT,
action VARCHAR(50),
question_id INT,
answer_id INT,
q_num INT,
timestamp INT
);
插入数据：
INSERT INTO survey_log VALUES(5, 'show', 285, NULL, 1,123);
INSERT INTO survey_log VALUES(5, 'answer', 285, 124124, 1,124);
INSERT INTO survey_log VALUES(5, 'show', 369, NULL, 2,125);
INSERT INTO survey_log VALUES(5, 'skip', 369, NULL, 2,126);
选取：
SELECT survey_log
FROM(
SELECT t.survey_log, dense_rank() over (ORDER BY ratio DESC) as 'Rank'
FROM(
SELECT s.question_id as survey_log,
(CASE
WHEN SUM(s.action = 'answer') = 0 then 0
else SUM(s.action = 'answer') / COUNT(*)
END) as ratio
FROM survey_log as s
GROUP BY uid, question_id) as t
) as p
WHERE p.Rank = 1;
结果：
项目十八：各部门前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高工资的员工功能。
DROP TABLE IF EXISTS employee;
DROP TABLE IF EXISTS department;
创建表;
CREATE TABLE Employee (
Id int,
Name varchar(255),
Salary int,
DepartmentId int
);
CREATE TABLE Department (
Id int,
Name varchar(255)
);
Truncate table Employee;
插入数据：
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 Employee (Id, Name, Salary,DepartmentId) VALUES ('1', 'Janet', '69000', '1');
INSERT INTO Employee (Id, Name, Salary,DepartmentId) VALUES ('1', 'Randy', '85000', '1');
INSERT INTO Department (Id, Name) VALUES('1', 'IT');
INSERT INTO Department (Id, Name) VALUES('2', 'Sales');
选取：
SELECT d.Name AS Department, e1.Name AS Employee, e1.Salary
FROM Employee e1 JOIN Department d ON e1.DepartmentId = d.Id
WHERE 3 > (SELECT COUNT(DISTINCT e2.Salary) FROM Employee e2
WHERE e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId);
结果：
参考：
https://www.cnblogs.com/an5456/p/10478949.html
https://blog.youkuaiyun.com/qq_40803710/article/details/80303072
项目十九：平面上最近距离
point_2d 表包含一个平面内一些点（超过两个）的坐标值（x，y）。
写一条查询语句求出这些点中的最短距离并保留2位小数。
| x | y |
|---|---|
| -1 | -1 |
| 0 | 0 |
| -1 | -2 |
最短距离是1，从点（-1，-1）到点（-1，2）。所以输出结果为：
| shortest |
|---|
| 1.00 |
注意： 所有点的最大距离小于10000。
创建表：
DROP TABLE IF EXISTS Trips;
CREATE TABLE point_2d(
x INT,
y INT
);
插入数据：
INSERT INTO POINT_2d VALUES(-1,-1);
INSERT INTO POINT_2d VALUES(0,0);
INSERT INTO POINT_2d VALUES(-1,-2);
选取：
SELECT MIN(distance) as shortest
FROM(
SELECT POW ((p1.x - p2.x),2) + POW((p1.y - p2.y), 2) as distance
FROM point_2d as p1
INNER JOIN point_2d as p2
ON !(p1.x = p2.x and p1.y = p2.y)
) as p;
结果：
项目二十：行程和用户（难度：困难）
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 |
±-----------±------------------+
创建表：
DROP TABLE IF EXISTS Trips;
DROP TABLE IF EXISTS Users;
CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS Users (
Users_Id INT,
Banned VARCHAR(50),
Role ENUM('client', 'driver', 'partner')
);
插入数据：
TRUNCATE TABLE Trips;
INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('1', '1', '10', '1', 'completed', '2013-10-01');
INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('2', '2', '11', '1', 'cancelled_by_driver', '2013-10-01');
INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('3', '3', '12', '6', 'completed', '2013-10-01');
INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('4', '4', '13', '6', 'cancelled_by_client', '2013-10-01');
INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('5', '1', '10', '1', 'completed', '2013-10-02');
INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('6', '2', '11', '6', 'completed', '2013-10-02');
INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('7', '3', '12', '6', 'completed', '2013-10-02');
INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('8', '2', '12', '12', 'completed', '2013-10-03');
INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('9', '3', '10', '12', 'completed', '2013-10-03');
INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('10', '4', '13', '12', 'cancelled_by_driver', '2013-10-03');
TRUNCATE TABLE Users;
INSERT INTO Users (Users_Id, Banned, Role) VALUES ('1', 'No', 'client');
INSERT INTO Users (Users_Id, Banned, Role) VALUES ('2', 'Yes', 'client');
INSERT INTO Users (Users_Id, Banned, Role) VALUES ('3', 'No', 'client');
INSERT INTO Users (Users_Id, Banned, Role) VALUES ('4', 'No', 'client');
INSERT INTO Users (Users_Id, Banned, Role) VALUES ('10', 'No', 'driver');
INSERT INTO Users (Users_Id, Banned, Role) VALUES ('11', 'No', 'driver');
INSERT INTO Users (Users_Id, Banned, Role) VALUES ('12', 'No', 'driver');
INSERT INTO Users (Users_Id, Banned, Role) VALUES ('13', 'No', 'driver');
选取：
SELECT
Request_at AS DAY,
ROUND( SUM( CASE WHEN `Status` LIKE 'cancelled%' THEN 1 ELSE 0 END ) / COUNT( * ), 2 ) AS cancelled_rate
FROM
trips
LEFT JOIN ( SELECT Users_Id, Banned FROM users ) client ON trips.Client_Id = client.Users_Id
LEFT JOIN ( SELECT Users_Id, Banned FROM users ) driver ON trips.Driver_Id = driver.Users_Id
WHERE client.Banned = 'NO' AND driver.Banned = 'NO'
GROUP BY Request_at
ORDER BY Request_at;
结果：
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
