总结
找第二高的薪水,没有第二高的薪水时,返回null。
我用了rank()函数,但是当没有第二高的薪水时,返回的是空,不符合要求。
看了下解决方案,个人感觉最好的是用limit或limit+offset来做
limit n,m,意为跳过开头的n位,从第n+1位开始取,一共取m个。
limit n offset m,意为跳过开头的m位,从第m+1开始取,一共取n个。
目录
题目
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 query should return 200
as the second highest salary. If there is no second highest salary, then the query should return null
.
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
解法一
SELECT max(Salary)
FROM Employee
WHERE Salary < (SELECT max(Salary) FROM Employee)
解法二
select (
select distinct Salary from Employee order by Salary Desc limit 1 offset 1
)as second
limit n,m,意为跳过开头的n位,从第n+1位开始取,一共取m个。
limit n offset m,意为跳过开头的m位,从第m+1开始取,一共取n个。
开始忘记加distinct了,就没通过,因为当最大的有重复值时,limit 1 offset 1取到的其实还是最大的。手头mysql环境不方便,用impala查了执行计划,是先group by去重,再排序,所以加了distinct可以满足要求。
rank解法(wrong)
select a.Salary as SecondHighestSalary from
(select Salary,rank() over (order by Salary desc) as rankNum
from Employee) a
where a.rankNum=2