leetcode地址:
这个问题很有趣,是要求我们写个sql来查询Employee表里第二高的工资,如果没有第二高的,那么返回null。
+----+--------+
| Id | Salary |+----+--------+| 1 | 100 || 2 | 200 || 3 | 300 |+----+--------+
看到这个问题,可能很多人会想,这很简单啊,写个order by desc,然后找到第二个即可。
select Salary from Employee order by Salary desc limit 1,1
试试提交呗?Wrong answer,为什么?看条件约束啊,没有第二要返回null,我看到null的第一直觉是通过join搞到null值,于是有了下面的ac sql:
select max(Salary) as SecondHighestSalary from( select o1.* ,case when o2.s is null then 1 else 0 end as nt from (select * from Employee)o1 left outer join (select max(Salary) as s from Employee)o2 on(o1.Salary=o2.s) )t where nt=1
思路简单说就是通过全表左外联最大salary,从关联不到的salary里再找最大不就是第二大吗?
最后的结果是894ms,当然我坚信有很多更快更高效的结果。
myself:
oracle中使用rownum不能实现,因为如果只有一条记录则会把这条记录做为最后的结果返回。使用rownum的sql:
select * from (select * from (select * from employee eorder by e.salary desc) t1where rownum<3)t2where rownum<2order by t2.salary asc
参考Change Dir,使用oracle时的另一种写法:
select max(salary) SecondHighestSalary from (select o1.*,case when o2.s is null then 1 else 0 end statusfrom(select * from employee) o1,(select max(salary) s from employee)o2where o1.salary=o2.s(+)) where status=1
http://www.blogjava.net/changedi/archive/2015/01/27/422478.html