用Python刷Leetcode

在MySQL数据库中建立一个名为leetcode的数据库：
mysql>create database if not exist leetcode;
查看数据库的编码方式：
mysql>show create database leetcode;
刷题的时候如果有必要，可以针对不同的题在leetcode数据库下创建不同的表，用来尝试解题。

以下的解题思路有些参考为网络

175. Combine Two Tables
解体思路其一：

# Write your MySQL query statement below
select FirstName, LastName, City, State
from Person left join Address on Person.PersonId = Address.PersonId;```

[176. Second Highest Salary](https://leetcode.com/problems/second-highest-salary/)
解体思路其一：

Write your MySQL query statement below

select max(Salary) as SecondHighestSalary #整个别名防止Wrong Answer
from Employee
where Salary < (select max(Salary) from Employee);“`

177. Nth Highest Salary
解题思路其一：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1  
# ORDER BY Salary DESC LIMIT M, 1  表示降序排序除去最高M位之后的第一个数
  );
END```

[178. Rank Scores](https://leetcode.com/problems/rank-scores/)
解题思路其一：

Write your MySQL query statement below

select s.Score, count(distinct t.Score) Rank
from Scores s join Scores t on s.Score <= t.Score #join 和 inner join是
group by s.Id
order by s.Score desc;“`
在MySQL中，left join 称为left outer join，right join称为right outer join。

180. Consecutive Numbers
这里使用两种方式：
第一种使用select-from-where：

SELECT DISTINCT L1.Num
FROM Logs L1, Logs L2, Logs L3
WHERE (L1.Id = L2.Id + 1 AND L1.Num = L2.Num) AND (L1.Id = L3.Id + 2 AND L1.Num = L3.Num);```
第二种使用Join子句:

SELECT DISTINCT L1.Num
FROM Logs L1
JOIN Logs L2 ON L1.Id + 1 = L2.Id
JOIN Logs L3 ON L1.Id + 2 = L3.Id
WHERE L1.Num = L2.Num AND L1.Num = L3.Num
ORDER BY L1.Num“`

上面的答案点击了Submit Solution会报如下错误：

Submission Result: Wrong Answer More Details  
Input:{"headers": {"Logs": ["Id""Num"]}
"rows": {"Logs": []}}
Output:
{"headers": ["Num"], "values": []}
Expected:
{"headers": ["ConsecutiveNums"], "values": []}

解决办法：
将第一条SELECT DISTINCT L1.Num改为SELECT DISTINCT L1.Num as ConsecutiveNums，然会显示：Submission Result: Accepted

181. Employees Earning More Than Their Managers
解题思路其一：

# Write your MySQL query statement below
select E1.Name as Employee     #加别名防止出现Wrong Answer
from Employee as E1, Employee as E2 
where E1.ManagerId = E2.Id and E1.Salary > E2.Salary;```

[182. Duplicate Emails](https://leetcode.com/problems/duplicate-emails/)
思路其一：

Write your MySQL query statement below

select Email
from Person
group by Email
having count(*) > 1;“`
思路其二：

# Write your MySQL query statement below
select distinct a.Email 
from Person a join Person b
on a.Email = b.Email
where a.Id <> b.Id;```

[183. Customers Who Never Order](https://leetcode.com/problems/customers-who-never-order/)
两种解题思路：

Write your MySQL query statement below

思路1

SELECT A.Name as Customers from Customers A
LEFT outer JOIN Orders B on A.Id = B.CustomerId
WHERE B.CustomerId is NULL

思路2

SELECT A.Name as Customers from Customers A
WHERE A.Id NOT IN (SELECT B.CustomerId from Orders B)“`

184. Department Highest Salary
解题思路其一：

# Write your MySQL query statement below
select d.Name as Department, e.name as Employee, e.Salary
from Employee e, Department d
where e.DepartmentId = d.Id 
and (DepartmentId, Salary) in 
(select DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId);```
[185. Department Top Three Salaries](https://leetcode.com/problems/department-top-three-salaries/)
解题思路其一：

Write your MySQL query statement below

select d.Name Department, e1.Name Employee, e1.Salary
from Employee e1, Department d
where e1.DepartmentId = d.Id and
(select count(distinct(e2.Salary)) from Employee e2 where e2.Salary > e1.Salary and e1.DepartmentId = e2.DepartmentId) < 3;“`

196. Delete Duplicate Emails
其思路如下，delete有点像select。

# Write your MySQL query statement below
delete p1 
from Person p1, Person p2
where p1.Email = p2.Email and p1.Id > p2.Id;```

[197. Rising Temperature](https://leetcode.com/problems/rising-temperature/)
解题思路其一：

Write your MySQL query statement below

select w1.Id
from Weather w1, Weather w2
where w1.Temperature > w2.Temperature and TO_DAYS(w1.DATE)-TO_DAYS(w2.DATE)=1;

用了to_days函数。

[262. Trips and Users](https://leetcode.com/problems/trips-and-users/)
解题思路其一：

Write your MySQL query statement below

select t.Request_at as Day,
round(sum(t.Status like ‘cancelled_%’)/count(*), 2) as ‘Cancellation Rate’

上面的别名’Cancellation Rate’因为是两个词，要用单引号引起来。

from Trips t join Users u on t.Client_Id = u.Users_Id
where u.Banned=’No’ and t.Request_at >= “2013-10-01” and t.Request_at <= “2013-10-03”
group by t.Request_at;
“`

你好