码疯窝

LeetCode 每日一题 — Second Highest Salary

2015/01/13 10:54:06    分类: 日志连载    0人评论 次浏览

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.

分析: 这题解法很多, 一种是很普遍的思路两次查询, 一次查出最高分, 再一次查出小于此最高分的最高分. 另一种为DISTINCT 去重后, 排序, 然后用LIMIT 取第二个. 此时要额外嵌套一个SELECT, 这样在没有第二个的时候才可以返回NULL

方法一:

SELECT MAX(Salary) FROM Employee
WHERE Salary < (
    SELECT MAX(Salary) FROM Employee
);

方法二:

SELECT (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1,1);
继续查看有关 日志连载的文章

0个访客评论