Datawhale-MySQL-第5期-Day 7-MySQL 复杂项目实战

1.

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 t.Status LIKE 'CANCELLED%' 
                                     THEN 1 ELSE 0 END))
                                     /COUNT(*),2) AS 'CANCELLATION RATE'
FROM TRIPS T
      JOIN USERS U
      ON U.BANNED = 'NO' AND U.USERS_ID = T.CLIENT_ID
WHERE  T.REQUEST_AT BETWEEN '2013-10-01' AND '2013-10-31'
GROUP BY T.REQUEST_AT;

2.

SELECT D.NAME DEPARTMENT, E1.NAME EMPLOYEE, E1.SALARY 
FROM DEPARTMENT D JOIN EMPLOYE E1
ON  E1.DEPARTMENTID = D.ID   
WHERE (SELECT COUNT(DISTINCT e2.SALARY)
       FROM EMPLOYE E2
       WHERE E2.SALARY > E1.SALARY AND E1.DEPARTMENTID = E2.DEPARTMENTID
       ) <3
ORDER BY D.NAME, E1.SALARY DESC;

3. 

SELECT S2.SCORE, (SELECT COUNT(S1.SCORE) + 1
								  FROM SCORE S1
								  WHERE S1.SCORE > S2.SCORE) AS 'RANK'
FROM SCORE S2
ORDER BY S2.SCORE DESC;