一文搞定Leetcode数据库

175. 组合两个表
题目要求保留Person表的所有行数，因此使用Person表在前的左连接组合两个表。

SELECT
    p.FirstName, p.LastName, a.City, a.State
FROM
    Person p LEFT JOIN Address a
ON
    p.PersonId = a.PersonId

176. 第二高的薪水
使用LIMIT实现第N个数据的查询，但是由于表中可能只存在一个数据，此时使用LIMIT不会返回查询结果，若要实现不存在时查询结果为NULL，需要将查询结果作为一个表格并再次进行一次查询。

SELECT
IFNULL(
    (SELECT
    DISTINCT Salary 
FROM
    Employee
ORDER BY
    Salary DESC
LIMIT 1,1), NULL
) AS SecondHighestSalary

177. 第N高的薪水
与176类似，这里题目使用了存储函数实现，可以不用在外面套一层SELECT也可以返回null值，要提前给N减1，否则会报错。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N = N-1;
  RETURN (
      # Write your MySQL query statement below.
    SELECT
        DISTINCT Salary
    FROM
        Employee
    ORDER BY Salary DESC
    LIMIT N,1
  );
END

178. 分数排名
使用SQL内置的排序函数实现，注意要在over方法中指定排序的顺序。

SELECT
    Score, 
    dense_rank()over(ORDER BY Score DESC) AS `Rank`
FROM
    Scores

180. 连续出现的数字
使用三个表的自连接，用来实现连续三行相等的条件筛选。

SELECT
    DISTINCT l1.Num AS ConsecutiveNums
FROM
    Logs l1, Logs l2, Logs l3
WHERE
    l1.Num = l2.Num AND l2.Num = l3.Num
    AND l1.Id = l2.Id-1 AND l2.Id = l3.Id-1

181. 超过经理收入的员工
使用自连接可简单实现

SELECT
    e1.Name AS Employee
FROM
    Employee e1, Employee e2
WHERE
    e1.ManagerId = e2.Id AND e1.Salary > e2.Salary

182. 查找重复的电子邮箱
使用GROUP BY进行分裂并使用HAVING + COUNT() 对每个邮箱地址进行计数，最后筛选出出现此时>1的邮箱地址。

SELECT
    Email
FROM
    Person
GROUP BY Email
HAVING
    COUNT(Email) >1

183. 从不订购的客户
使用左连接并筛选出订单Id为空的行即可。

SELECT
    c.Name AS Customers
FROM
    Customers c LEFT JOIN Orders o 
ON
    c.id = o.CustomerId
WHERE
    o.Id IS NULL

184. 部门工资最高的员工
注意IN的用法，可以将多个字段合并成一个整体然后用IN进行筛选；
IN后跟的子查询用于对部门进行分类。当我们要查询xxx中最xxx的数据时，通常可以采用GROUP BY关键字来进行分类。

SELECT 
    d.name Department,
    e.name Employee, 
    e.salary 
FROM 
    Employee e INNER JOIN Department d  
ON 
    e.DepartmentId=d.Id 
AND 
    (e.salary, e.DepartmentId) IN 
        (
            SELECT 
                MAX(e.salary) AS salary, e.DepartmentId 
            FROM 
                Employee e 
            GROUP BY 
                DepartmentId
        )

185. 部门工资前三高的所有员工
解法一：
类似184的解法，利用子查询计算当前salary是否属于同部门下前三大的工资，对于每一个e.salary都要进行一次 SELECT COUNT() 查询，效率较低。

SELECT 
    d.name Department,
    e.name Employee, 
    e.salary 
FROM 
    Employee e INNER JOIN Department d  
ON 
    e.DepartmentId=d.Id 
WHERE 
    3> 
        (
            SELECT 
                COUNT(DISTINCT Salary)
            FROM 
                Employee e2
            WHERE
                e.Salary < e2.Salary AND e.DepartmentId = e2.DepartmentId
        )

解法二：
利用窗口函数，本处使用rank() 或 dense_rank() 均可，MySQL的窗口函数细节可以参考：MySQL操作实战（二）：窗口函数。
利用窗口函数我们可以通过一次查询将Employee表格中的满足要求：每个部门获得前三高工资的所有员工 的数据查询出来得到一个表格a，然后将表格a和Deparment表格通过id联结即可得到最终的答案。

select d.name AS Department,a.Name As Employee, a.Salary AS Salary
from (
   select *, 
          dense_rank() over (partition by DepartmentId
                       order by Salary desc) as ranking
   from Employee) as a, Department as d
WHERE a.DepartmentId = d.Id 
AND ranking <= 3;

196. 删除重复的电子邮箱
要求使用DELETE语句实现。
使用自连接创建Person表的笛卡尔积，然后将不符合题目要求的数据删除即可，注意这里使用的条件p1.id>p2.id 会将除了id最小以外的所有数据删除。

DELETE
    p1 
FROM 
    Person p1, Person p2
WHERE
    p1.Email = p2.Email AND p1.id>p2.id;

197. 上升的温度
需要选择前一天的数据，使用DATEDIFF函数，函数返回结果为两个参数相差的时间，单位为day。

SELECT 
    w1.id 
FROM 
    Weather as w1 join Weather as w2  
ON 
    DATEDIFF(w1.RecordDate, w2.RecordDate) = 1 
AND 
    w1.Temperature > w2.Temperature 

262. 行程和用户
此题难点在于计算取消率，想了很久用COUNT函数没有做出来，一看题解区真是惊为天人了，以简单的AVG函数就轻松搞定了，SQL中的聚合函数是可以将布尔类型的变量直接转化为数值类型进行运算的，因此直接AVG(t.Status!="Completed") 既可以求出完成率，再有ROUND函数截取有效数字即可。

SELECT
    t.Request_at 'Day', ROUND(AVG(t.Status!="Completed"),2) AS 'Cancellation Rate'
FROM
    Trips t JOIN Users u
ON
    t.Client_Id = u.Users_Id AND u.Banned != "Yes"
    OR (t.Client_Id = u.Users_Id AND u.Banned != "Yes")
WHERE 
    t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY 
    t.Request_at

595. 大的国家
WHERE子句的基本使用，多个筛选条件可以用AND或者OR关键字连接。

SELECT
    w.name, w.population, w.area
FROM
    `World` AS w
WHERE 
    w.area > 3000000
OR
    w.population > 25000000

596. 超过5名学生的课
本题考查GROUP BY关键字的使用，使用COUNT函数计算每个class分类下的学生数量时，需要使用DISTINCT关键字去重。

SELECT 
    class
FROM
    courses
GROUP BY
    class
HAVING
    COUNT(DISTINCT student)>4

601. 体育馆的人流量
比较简单的做法是使用三个表的自连接，但这种做法在数据量较大时效率低下，应该还有更好的做法，待补充。

SELECT
    DISTINCT s1.id, s1.visit_date, s1.people
FROM
    stadium s1, stadium s2, stadium s3
WHERE
    (s1.people >= 100 AND s2.people >=100 AND s3.people >=100)
    AND(    
        (s1.id = s2.id-1 AND s2.id = s3.id-1)
        OR (s1.id = s2.id-1 AND s1.id = s3.id+1)
        OR (s1.id = s2.id+1 AND s2.id = s3.id+1) 
    )
ORDER BY s1.id

620. 有趣的电影
比较简单，考查WHERE语句多个筛选条件的使用，以及SQL中的求余操作，可用MOD函数或%运算符。

SELECT
    *
FROM
    cinema AS c
WHERE
    c.description != "boring" AND MOD(c.id, 2)=1
ORDER BY c.rating DESC

626. 换座位
本题考查多重条件判断 CASE WHEN语句的使用，在SQL中使用CASE语句能得到比IF语句更加可读的SQL代码，在CASE语句中，WHEN 判断条件 THEN 查询结果 为固定语法，注意末尾要使用END关键字结束CASE语句。

SELECT 
(CASE
    WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
    WHEN MOD(id,2) = 1 THEN id+1
    ELSE id-1
END) AS id, student 
FROM seat
ORDER BY id;

627. 交换工资
与上一题类似，考查CASE语句的使用。

UPDATE  
    salary
SET
    sex = 
    (CASE
        WHEN sex = "m" THEN "f"
        else "m"
    END);