修改 数据库.md

Miller-Xie · Miller-Xie · commit adc194ce1dd2 · 2019-03-04T10:15:51.000+08:00
diff --git a/LeetCode/数据库.md b/LeetCode/数据库.md
@@ -134,6 +134,7 @@ SELECT Email FROM Person GROUP BY Email HAVING COUNT(Email) >= 2;
 
 ```mysql
 # Write your MySQL query statement below
+
 ```
 
 
@@ -177,7 +178,7 @@ SELECT Email FROM Person GROUP BY Email HAVING COUNT(Email) >= 2;
 
 ```mysql
 # Write your MySQL query statement below
-SELECT * FROM cinema WHERE description != 'boring' AND id%2 = 1 
+SELECT * FROM cinema WHERE description != 'boring' AND id % 2 = 1 
 ORDER BY rating DESC;
 ```
 
@@ -224,6 +225,8 @@ FirstName, LastName, City, State
 
 ### 解答
 
+考察左连接 `LEFT JOIN` 的用法
+
 ```mysql
 # Write your MySQL query statement below
 SELECT FirstName,LastName,City,State FROM Person LEFT JOIN Address ON Person.PersonId = Address.PersonId;
@@ -260,13 +263,19 @@ SELECT FirstName,LastName,City,State FROM Person LEFT JOIN Address ON Person.Per
 
 ### 解答
 
+#### 方法1
+
+对同一个表进行**连接**，过滤条件是 `e1.ManagerId = e2.Id AND e1.Salary > e2.Salary`。
+
 ```mysql
 # Write your MySQL query statement below
 SELECT e1.name AS Employee FROM Employee AS e1,Employee AS e2 
 WHERE e1.ManagerId = e2.Id AND e1.Salary > e2.Salary;
 ```
 
+#### 方法2
 
+思路同**方法1**，连接方式变为`join...on...`。
 
 ```mysql
 # Write your MySQL query statement below
@@ -321,6 +330,8 @@ on a.managerid=b.id and a.salary>=b.salary;
 
 #### 方法1
 
+利用子查询过滤，找出表`Customers`中的`Id`不在表`Orders`的`CustomersId`中的那些行。
+
 ```mysql
 # Write your MySQL query statement below
 #select a.name as Customers from customers a join orders b on a.id = b.customerid;
@@ -331,6 +342,8 @@ where id not in
 
 #### 方法2
 
+对`Customers`和`Orders`两个表进行左连接，连接条件是`Customers.Id = Orders.CustomersId`，那么连接之后`Orders`表中`Id`列为空的那些行即为所求。
+
 ```mysql
 # Write your MySQL query statement below
 select c.name as Customers from customers as c left join orders as o
@@ -370,7 +383,9 @@ Id 是这个表的主键。
 
 ### 解答
 
-由于 *MYSQL* 不能先`select`一个表的记录，然后在按此条件进行更新和删除同一个表的记录删除数据。因此创建临时表进行过渡
+对于重复的邮箱，保留`Id`最小的，相当于是以`Email`作为分组，保留每一组`Id`最小的那些行。
+
+**注意**：由于 *MYSQL* 不能先`select`一个表的记录，然后在按此条件进行更新和删除同一个表的记录删除数据。因此创建临时表进行过渡。
 
 ```mysql
 # Write your MySQL query statement below
@@ -410,13 +425,21 @@ delete from person where id not in
 
 ### 解答
 
+对同一个表`Weather`创建两个临时表`a`和`b`进行连接，连接条件是：`a`的`recorddate`比`b`的`recorddate`大1并且`a`的`temperature`大于`b`的`temperature`。
+
+#### 方法1
+
+利用`datediff`函数
+
 ```mysql
 # Write your MySQL query statement below
 select a.id from weather as a join weather as b
 on datediff(a.recorddate,b.recorddate) = 1 and a.temperature > b.temperature;
 ```
 
+#### 方法2
 
+利用`subdate`函数。
 
 ```mysql
 # Write your MySQL query statement below
@@ -467,6 +490,10 @@ on (a.temperature > b.temperature and subdate(a.RecordDate,1) = b.RecordDate);
 
 ### 解答
 
+以课程`class`进行分组，过滤出学生数不小于5的即可。
+
+**注意**：加上`distinct`关键字过滤重复的学生。
+
 ```mysql
 # Write your MySQL query statement below
 select class from courses group by class having count(distinct student) >= 5;
