算法题（MySql）：题目来源（LeetCode）

175. 组合两个表

select FirstName, LastName, City, State from Person left join Address on Person.PersonId = Address.PersonId

176. 第二高的薪水

#将不同的薪资按降序排序，然后使用 LIMIT 子句获得第二高的薪资。
#方法一
SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary
#方法二，ifnull，为真，返回null
select ifnull 
	(( select distinct(Salary) from Employee order by Salary desc
    limit 1 ,1),null )as SecondHighestSalary

177. 第N高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
      set n = N - 1;
  RETURN (
      select ifnull (( select distinct(Salary) from Employee order by Salary desc
    limit n ,1),null )as SecondHighestSalary
  );
END

181. 超过经理收入的员工

select a.Name AS Employee from Employee AS a join Employee AS b on a.ManagerId = b.Id where a.Salary > b.Salary

182. 查找重复的电子邮箱

#方法1
select Email from Person group by Email having count(Email) > 1
#方法2
select  distinct a.Email from Person as a , Person as b where a.Email = b.Email and a.Id != b.Id

183. 从不订购的客户

select a.Name as Customers from Customers as a left join Orders on a.Id = Orders.CustomerId where Orders.CustomerId is null

184. 部门工资最高的员工

select 
    d.Name as Department,
    e.Name as Employee,
    e.Salary
from
    Employee as e, Department as d
where 
    e.DepartmentId = d.Id
    and
    (e.Salary, e.DepartmentId) in (select max(Salary),DepartmentId from Employee group by DepartmentId)

196. 删除重复的电子邮箱

delete p1 from Person as p1 ,Person as p2
where p1.Email = p2.Email and p1.Id > p2.Id