软件工程师数据库面试技巧之 SQL中的第二名记录 Software Engineer Interview Question

Question: Write a SQL query to get the second highest salary from the Employee table.

现在最吃香的工程师是全栈工程师 (Full Stack), 因此你除了要好的算法数据结构知识外你还需要懂数据库等计算机知识. 有人说, SQL好简单, 其实SQL也可以考考你的逻辑, 比如有这么一个简单的 (含有两个字段, 三行记录) 的关系表 (假设表名为 Employee).

+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100    |
| 2 | 200    |
| 3 | 300    |
+----+--------+

请输出第二高的记录, 也就是 ID = 2, Salary = 200.

如果不存在第二高, 比如两条记录都是 100, 100, 那么应该返回 null. 而 100, 100, 90 应该返回90而不是100. 这样的话 " select * from Employee order by salary desc limit 1,1 " 就不对了.

Another example, Input Salary Array = [100, 100], the output should be null; When array equals [100, 100, 90]. The output should be 90 (second highest salary) instead of 100. Therefore the common mistake is the following:

你也可以用 group by:

Some might correct this via group by:

select Salary as SecondHighestSalary from Employee group by Salary order by Salary limit 1,1

但是当第二高记录不存在时, 该SQL返回空而不是 null.

However, it will return empty instead of null when it does not exist.

Input: {"headers": {"Employee": ["Id", "Salary"]}, "rows": {"Employee": [[1, 100]]}} Output: {"headers": ["SecondHighestSalary"], "values": []} Expected: {"headers": ["SecondHighestSalary"], "values": [[null]]}

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.

The second largest number is always smaller than the largest number. We use a inner SQL to get the maximum salary and we just need to get the largest number that is smaller than this largest number.

so, if the numbers are 100, 100, 90, it should return 90 instead of 100, which makes " select * from Employee order by salary desc limit 1,1 " incorrect.

SQL中有 max, min, 但这些都无法直接输出第二高(或者第二低), 但你可以这么想, 我取得了最高的, 剩下的取最高就是第二高了, 是不是很简单?

select max(Salary)
from Employee
where Salary < (select max(Salary) from Employee)

That is it, no tricks, no sorting, just two SQL selects and two group functions max

上面黑体的是子查询先返回最大的记录为 300, 那么接下来意思就很明白了, 比最大值300小的最大值是多少? 200嘛.

PS: 当年我招程序员的面试的时候就拿了这一题作为第一题 (45分钟考卷, 10题左右, 这题是送分题)

Originally published at https://steemit.com Thank you for reading my post, feel free to FOLLOW and Upvote @justyy which motivates me to create more quality posts.

原创首发于 https://steemit.com 非常感谢阅读, 欢迎FOLLOW和Upvote @justyy 能激励我创作更多更好的内容.

// 已同步到我的中文博客和英文算法博客。

近期热贴 Recent Popular Posts

软件工程师数据库面试技巧之 SQL中的第二名记录 Software Engineer Interview Question - The Second Highest