Write a SQL query to get the second highest salary from the Employee
table.
1 |
+----+--------+ |
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
.
1 |
+---------------------+ |
思路:
按薪水salary
倒序排序,然后从第一个开始,取下一个。
一开始我的SQL(Wrong Answer
)
1 |
SELECT DISTINCT Salary as SecondHighestSalary |
忽略了如果表中salary
的值只有一种的情况,这样就没有the second highest salary
,输出 null
.
正确写法:
1 |
SELECT IFNULL( |
IFNULL
这儿用到了 MySQL 的 IFNULL(expr1,expr2)
函数,如果 expr1 不是 NULL,IFNULL() 返回 expr1,否则它返回 expr2。
近期评论