leetcode-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.

思路：

如果是选出所有Salary中最大的，语句是：

select max(Salary)
from Employee

 理解了这个，那么第二大的就是在除去最大的Salary中选择最大的Salary

同样的道理，我们可以选出第三大的和第四大的etc.

选择第三大的代码如下：

select max(Salary)
from Employee
where salary < (
     select max(Salary)
     from Employee
     where Salary <>
      (selcet max(Salary) from Employee)
)

 或者是：

select max(Salary)
from Employee
where Salary <> (
select max(Salary)
from Employee
)
and Salary <>(
select max(Salary)
from Employee
where Salary<>(select max(Salary) from Employee) 
)

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

leetcode-Second Highest Salary

原文：http://www.cnblogs.com/immortal-worm/p/5078809.html