175. Combine Two Tables

1.left join

select p.FirstName, p.LastName, a.City, a.State from Person p LEFT JOIN Address a ON p.PersonId = a.PersonId

2.left join + using

select p.FirstName, p.LastName, a.City, a.State from Person p LEFT JOIN Address a using(PersonId)

3.natural left join

select p.FirstName, p.LastName, a.City, a.State from Person p NATURAL LEFT JOIN Address a

176. Second Highest Salary

使用max函数

select max(Salary) as SecondHighestSalary
from Employee
where Salary < (select max(Salary) from Employee) 

普通解法

select (
    select distinct Salary 
    from Employee 
    order by Salary desc 
    limit 1,1
) as SecondHighestSalary

MySQL 5 支持limit的另一种替代写法

select (
    select distinct Salary 
    from Employee 
    order by Salary desc 
    limit 1 offset 1
) as SecondHighestSalary

limit n offset m 表示从行m开始的n行数据，第一行数据为行0

需要注意的是

select distinct Salary as SecondHighestSalary 
from Employee 
order by Salary desc 
limit 1 offset 1

这种写法返回的是一个空值而不是NULL

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 1 offset M
  );
END

和上一个差不多的题，需要注意的是行是从0开始的，所以对于第N高的数据，其行值为N-1