数据库(SQL) 2016-08-16

[zhaokuohaha]

176. Second Highest Salary 177. Nth Highest Salary

[dayang]

# LeetCode 176 Second Highest Salary
# Write your MySQL query statement below
select MAX(salary) as SecondHighestSalary 
from Employee 
where salary < (select MAX(salary) from Employee);

[dayang]

# LeetCode 177  Nth Highest Salary
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  declare M INT;
  set M=N-1;
  RETURN (
      # Write your MySQL query statement below.
      select distinct salary from Employee order by salary desc limit M,1
  );
END

[zhaokuohaha]

select max(Salary) as SecondHighestSalary
from Employee 
where Salary < (select max(Salary) from Employee)

[wolfogre]

本来我想写的是

# Write your MySQL query statement below
select distinct Salary as SecondHighestSalary from Employee as A where (
    select count(*) from Employee as B where A.Salary < B.Salary
) = 1;

报错：

Input:
{"headers": {"Employee": ["Id"
"Salary"]}
"rows": {"Employee": [[1
100]]}}
Output:
{"headers": ["SecondHighestSalary"], "values": []}
Expected:
{"headers": ["SecondHighestSalary"], "values": [[null]]}

改成

select MAX(Salary) as SecondHighestSalary from Employee as A where (
    select count(*) from Employee as B where A.Salary < B.Salary
) = 1;

对了，坑爹呢这是。

[HaiyunC]

请问可以解释下Query getNthHighestSalary里面m是做什么用的嘛？

[wolfogre]

@HaiyunC 比如N为5，即去get 5th Highest Salary，则降序排序时要跳过前面M=N-1=4个，所以跳过4个，取1个，limit M,1