leetcode 171题:
查询第二高的薪水(salary)
----------------
ID salary
1 100
2 200
3 300
----------------
------------
secondHighestsalary
200
------------
方法一:排除最大工资,剩下的最大工资即为所求
select salary as secondHighersalary
from employee
where salary not in (select max(salary) from employee)
order by salary desc limit 1;
方法二:查询出最大的工资,然后查询剩下工资中比该记录小的最大数据记录
select max(salary)as secondHighersalary
from employee
where salary <(select max(salary) from employee)
# 方法三:
# 234ms,IFNULL,如果不为空返回第一个参数,为空返回null,降序后limit是关键,从第一条开始取,取一条,就是第二高工资
select IFNULL((select Distinct Salary from Employee order by Salary DESC limit 1,1),null) as SecondHighestSalary