leecode sql第176题:求第二高的薪水
首先需要将当前表种员工的工资进行排序,在之前的基础上会多出一个临时的字段。
select *, dense_rank() over(order by salary desc) as ranking from Employee
我们需要拿到临时字段的第二个数据,为了避免只有一个员工的情况,我们需要用到判断,ranking字段是否大于五条
select(if(max(ranking < 2), null, a.salary)) as SecondHighestSalary from (select *, dense_rank() over(order by salary desc) as ranking from Employee) a where a.ranking = 2
或者使用ifnull函数来判断是否有第二名的工资,我们首先要将工资按照从低到高排序,并且去重,并且使用limit和offset函数来确定是第二行
select distinct salary from employee order by salary desc limit 1 offset 1
然后再外面嵌套ifnull函数
select ifnull( (select distinct salary from employee order by salary desc limit 1 offset 1 ),null )as SecondHighestSalary