176. Second Highest Salary
来源:互联网 发布:windows微信登录 编辑:程序博客网 时间:2024/06/06 00:07
闲着没事到LeetCode上做做SQL的题目,结果被卡了半天。
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
.
翻译一下,表Employee
的结构像上面这样,查询表里Salary次高的那个,如果查不到就返回NULL。
就是这个返回null卡了我半天,我想查不到默认返回不就是null么,于是我写了这样的SQL : select Salary as SecondHighestSalary order by Salary desc limit 1,1;
结果过不了,返回的结果是空,而不是NULL。
那就加一个默认值吧,如果为空就返回默认值,那就是IFNULL函数了: select IFNULL(Salary, null) as SecondHighestSalary order by Salary desc limit 1,1;
结果还是不可以,返回仍为空,经过上网仔细查询一番因为order by Salary desc limit 1,1;
返回的是空,语句到这里就结束了,所以select后面紧跟的函数没有执行。
知道了原因,最终版本终于可以了: SELECT IFNULL((SELECT distinct Salary FROM Employee order by Salary desc limit 1,1) ,null) as SecondHighestSalary;
理解可能有误,望大家指出理解错误之处,多谢。
- 176. Second Highest Salary
- 176. Second Highest Salary
- 176. Second Highest Salary
- 176. Second Highest Salary
- 176. Second Highest Salary
- 176. Second Highest Salary
- 176. Second Highest Salary
- leetcode 176. Second Highest Salary
- leetcode 176. Second Highest Salary
- leetcode.176. Second Highest Salary
- leetcode 176. Second Highest Salary
- Leetcode 176. Second Highest Salary
- 176. Second Highest Salary#1
- leetcode 176. Second Highest Salary
- leetcode 176. Second Highest Salary
- 176. Second Highest Salary(Leetcode)
- [理解leetcode解法]176. Second Highest Salary
- 【leetcode Database】176. Second Highest Salary
- linux用VI编辑后保存退出命令
- 匹配正则
- 51NOD 1475 建设国家
- MVP
- 写时拷贝的方式实现C++中string类
- 176. Second Highest Salary
- 构造JSON格式数据方法总结
- 冒泡排序
- 明明的随机数
- LeetCode(3)Longest Substring Without Repeating Characters
- python处理数据
- 第一次到Bella家
- 看图写树(6-17)读后实现
- Area_poj1654_计算几何