7天快速掌握MySQL-DAY7

--code--
--建表并插入数据
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');
--
mysql> SELECT
    -> Request_at AS Day,
    -> FORMAT(sum(IF(Status LIKE 'cancelled%', 1, 0))/count(1), 2) AS 'Cancellation Rate'
    -> FROM Trips as t
    -> inner join Users as u on t.Client_Id = u.Users_Id
    -> where u.Banned = 'NO'
    -> GROUP BY Day;
+------------+-------------------+
| Day        | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33              |
| 2013-10-02 | 0.00              |
| 2013-10-03 | 0.50              |
+------------+-------------------+
3 rows in set (0.00 sec)

--code--
mysql> set @nn =3;  --设置前N高变量nn--
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT d.`Name` AS Department,e1.`Name` AS Employee,e1.Salary AS 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 e1.DepartmentId = e2.DepartmentId
    -> ) < @nn   --使用参数nn--
    -> ORDER BY d.`Name`,e1.Salary desc;
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      |  90000 |
| IT         | Randy    |  85000 |
| IT         | Joe      |  70000 |
| Sales      | Henry    |  80000 |
| Sales      | Sam      |  60000 |
+------------+----------+--------+
5 rows in set (0.00 sec)

--code--
mysql> SELECT FORMAT(Score,2) as Score,
    -> (SELECT count(*) FROM scores AS s2 WHERE s2.Score > s1.Score)+1 AS Rank
    -> FROM scores AS s1
    -> ORDER BY Score DESC;
+-------+------+
| Score | Rank |
+-------+------+
| 4.00  |    1 |
| 4.00  |    1 |
| 3.85  |    3 |
| 3.65  |    4 |
| 3.65  |    4 |
| 3.50  |    6 |
+-------+------+
6 rows in set (0.00 sec)