176. Second Highest Salary
#题目:
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.
#题解:
方法一:
select MAX(Salary) as SecondHighestSalary
from Employee
where Salary<(
select MAX(Salary)
from Employee
)
方法二:
select case
when count(Salary) >=1 then(
select distinct Salary
from Employee
order by Salary desc
limit 1,1)
else null
end as NthSalary
from Employee
#题释:
严谨写法:
SELECT IFNULL( (SELECTdistinct Salary as SecondHighestSalary FROM Employee orderby Salary desc limit 1,1) ,null);
SQL之limit用法
mysql支持limit
select * from tablename limit 0,1
即取出第一条记录。
select * from tablename limit 1,1
第二条记录
select * from tablename limit 10,20
从第11条到31条(共计20条)
注意mysql语法的IFNULL关键字的用法:
MYSQL IFNULL(expr1,expr2) 如果expr1不是NULL,IFNULL()返回expr1,否则它返回expr2。IFNULL()返回一个数字或字符串值,取决于它被使用的上下文环境。
---------------------
作者:wx-泡
来源:CSDN
原文:https://blog.csdn.net/qq_16468937/article/details/50780678
版权声明:本文为博主原创文章,转载请附上博文链接!