Second Highest Salary and Nth Highest Salary

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 query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

leetcode上面的题果然经典，这道题是让找出工资第二高的员工的工资。

题目难度不大，我用的是嵌套查询，第一层使用max函数找出最高工资，第二层匹配非最高工资，也就是在除了最高工资以外的工资里面再寻找一个最高工资也就是第二高工资了。

Mycode:

# Write your MySQL query statement below
select max(salary) SecondHighestSalary 
from employee 
where salary != (select max(salary) from employee);

这道题本身难度不大，但是想到如果有n个人的工资表，找出其中第m高的工资该怎么办呢？这时我的方法就行不通了。参看了网上大神的代码可以轻松求出第n高的工资。

理解一下大概就是：从最高工资里面找满足只有一个员工的工资比他高的这个工资。

要找第n高的工资直接把1改成n-1就行了。

select Max(Salary) from employee e1
where 1 = 
(select count(DISTINCT(e2.salary)) from employee e2 where e2.salary > e1.salary);

---

正好下一题就是

Nth Highest Salary。

Write a SQL query to get the nth highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

Mycode:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      select Max(salary) from employee e1
      where n-1 =
      (select count(distinct(salary)) from employee e2 where e2.salary > e1.salary)
  );
END

谢谢你的观看！