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) 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( (
SELECT
distinct
Salary
as
SecondHighestSalary
FROM
Employee
order
by
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()
返回一个数字或字符串值,取决于它被使用的上下文环境。
|