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

(*)

//

第二种方法，用到两个核心知识点：

select case when count(Salary) > 1then (    select distinct Salary    from Employee    order by Salary desc    limit 1,1)else nullend from Employee

（1）case when

 

（2）limit

limit m,n的含义是从选出该列从第m行开始的n行数元组

如果写个Select * from table limit 5, 10，很难一样就看出来到底哪个是limit，哪个是offset。我觉得应该尽量避免这种写法，需要offset的，就采用严谨的Select * from table limit 10 offset 5这样的格式；对于offset是0的，则直接省略，就写Select * from table limit 10好了。

//

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

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

选择第三大的代码如下：

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

 或者是：

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

---

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