主要参考四火的博客:
http://www.raychase.net/2810
--175 Combine Two Tables
--左连接
select p.FirstName, p.LastName, a.City, a.State
from Person p
left outer join Address a
on p.PersonId = a.PersonId;
--176 Second Highest Salary
--解法一 使用两次max找到第二大的作为过滤条件
select MAX(Salary)
from (SELECT Salary
from Employee
where Salary < (select MAX(Salary) from Employee ));
--解法二 mysql主要使用desc limit( m,n) 从m开始,的n个 初始下标是0
select IFNULL((select e.Salary
from Employee e
group by e.Salary
order by e.Salary desc limit 1, 1),
NULL) SecondHighestSalary;
--177 Nth Highest Salary
--取到第N大的 需要定义一个function了
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