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 |
+---------------------+
思路:
1、如果结果为空的话返回null,不为空则返回结果,这个条件可以用mysql的ISNULL函数来实现
2、结果仅为一条记录,需要用到分页关键字:limit,这里的条件设置为 limit 0,1(注意是0到1);
3、最后的记录名为SecondHighestSalary,我们在查询语句的最后需要加上一句:as SecondHighestSalary
4、第二高的薪水,我们可以通过将 select salary where salary < (select max(salary) form Employee)这个语句来获得所有比最大薪水小的薪水值,然后再通过order by desc来进行降序排列,然后用limit关键字来实现就行啦。
最后的结果如下:
# Write your MySQL query statement below
select IFNULL((
SELECT salary from employee where salary < (select max(salary) from employee) ORDER BY salary DESC LIMIT 0,1),
NULL) as SecondHighestSalary
网友评论