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

(*)

//

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

select 
case 
when count(Salary) > 1
then (
    select distinct Salary
    from Employee
    order by Salary desc
    limit 1,1
)
else null
end 
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 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)