Leetcode SQL: Nth Highest Salary

要熟练掌握declare， set等语句。
Solution 1:用group by

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare m int;
set m = n - 1;
  RETURN (
      # Write your MySQL query statement below.
      select salary from Employee 
      group by salary 
      order by salary desc limit m, 1
  );
END

Solution 2: 用distinct

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
  );
END

Leetcode SQL: Second Highest Salary

Write a SQL query to get the second highest salary from the Employee table.

+—-+——–+
| Id | Salary |
+—-+——–+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+—-+——–+
For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return null.
Solution 1:

select max(salary) from Employee
where salary < (select max(salary) from Employee);

Leetcode SQL: combine two tables

之前全用的小写，发现不能通过，后来发现Linux下mysql是区分大小写的，具体规则：
MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

　　1、数据库名与表名是严格区分大小写的；

　　2、表的别名是严格区分大小写的；

　　3、列名与列的别名在所有的情况下均是忽略大小写的；

　　4、变量名也是严格区分大小写的；
主要考察的是left join。注意：left join 和 left outer join是一样的。

select p.firstname, p.lastname, a.city, a.state 
from person p left outer join address a 
on p.personid = a.personid