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

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  能激励我创作更多更好的内容.  

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

•  SQL Coding Exercise – Second Highest Salary
•  软件工程师数据库面试技巧之 SQL中的第二名记录

 近期热贴 Recent Popular Posts 

•  第一次打肿脸充胖子 - 花了200STEEM租1万SP四周！  
• How to Claim BCC? BTC Hard-Fork via C program (Linux) 小白教程: 怎么领取 BCC (Bitcoin Cash) ? 
• 为了 SteemIt 开发了一个 中文简体和繁体自动切换的Chrome浏览器插件 Chrome Extension to Switch between Simplified Chinese and Traditional Chinese Automatically
• XY + XY = YZZ 大白 + 大白 = 白胖胖
• 24 Poker Game 24点扑克游戏
• Software Engineer Interview Question - Dynamic Programming - Integer Break 软件工程师面试技巧之 动态规化 - 整数拆分
• Technology-driven or Business-model-driven? 技术优先还是商业模式优先 – 献给在30多岁还在写代码的朋友们