MYSQL练习题：第二高的薪水（查找第N高的数）

其他相关的文章收集： SQL数据库语法及题目汇总

题目

Employee 表：

Column Name	Type
id	int
salary	int

id 是这个表的主键。表的每一行包含员工的工资信息。

Employee 表输入：

id	salary
1	100
2	200
3	300

编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null 。

进阶要求：编写一个SQL Function(函数) 来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询应该报告为 null 。

---

答案

方法一： MAX

使用MAX来找出最大值，同时使用两个MAX查询，其中一个作为筛选条件。剔除掉最大值，剩下的第二个MAX就是第二高的数。

SELECT MAX( salary ) SecondHighestSalary  
FROM Employee 
WHERE salary <( SELECT MAX( salary ) FROM Employee )

方法二： limit offset

使用子查询，对salary 进行降序排序后order by salary desc, 通过limit 1 offset 1找出第二高的数。

SELECT(
      (SELECT DISTINCT Salary    # distinct不能漏掉，排除重复值
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1)
) AS SecondHighestSalary

limit\offset语法：
1.offset X是跳过X个数据，limit Y是选取Y个数据
2.limit X,Y 中X表示跳过X个数据，读取Y个数据
详细介绍见：MYSQL查询第N个数（offset limit 用法）

方法三： limit

同上，外面需要再套一层，就可以输出null。

SELECT (      
SELECT DISTINCT Salary   
FROM Employee
 ORDER BY Salary DESC
LIMIT 1,1
)AS SecondHighestSalary

方法四：窗口函数dense_rank

select 
(
	select 
	salary 
	from 
		(select salary, dense_rank() over (order by salary desc) rn from Employee) t 
	where t.rn=2 limit 1
) SecondHighestSalary;

本题注意点：关于ifnull() 的问题

• 有的答案会在最外层使用ifnull()。 这个其实可以省略，因为直接用select时，默认查不到临时表会返回null。

#外层加ifnull()
SELECT
    IFNULL(
      (SELECT DISTINCT Salary 
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

• 但是不能够直接使用ifnull() 。
  因为limit 1 offset 1是从第2行开始取一行，如果没有第二行，不会返回任何东西，也不会返回null。
  ifnull是用来判断表格种的值是否为空值的，如果表格本身不存在数据，也就不会返回null。
  ifnull用法介绍

#错误写法：只有一行时不会返回null 
select ifnull(salary,null) 
from  Employee
order by salary desc
limit 1 offset 1

---

进阶版：Funciton 编写

问题：编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询应该报告为 null 。

Function 编写语法如下：

create function 函数名（参数列表） returns 返回类型
begin 
	函数体
End

详细介绍参考：MYSQL Function函数创建和调用

在了解了语法的使用方法后，我们就可以根据上面的查询代码，修改成对应的查询函数。

#方法2&3 limit
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;  #查询第7高的，就需要跳过7-1=6名
  RETURN (
      SELECT distinct salary
      FROM employee
      ORDER BY salary DESC
      LIMIT N, 1
  );
END

# 方法4窗口函数 
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
        SELECT 
            DISTINCT salary
        FROM 
            (SELECT 
                salary, dense_rank() over(ORDER BY salary DESC) AS rnk
             FROM 
                employee) tmp
        WHERE rnk = N
  );
END

更多答案解法见下面原网站链接：
题目链接：https://leetcode.cn/problems/second-highest-salary
题目链接：https://leetcode.cn/problems/nth-highest-salary/
答案参考：https://leetcode.cn/problems/nth-highest-salary/solution/mysql-zi-ding-yi-bian-liang-by-luanz/
来源：力扣（LeetCode）