LEETCODE--mysql第n高的薪水

最新推荐文章于 2022-12-06 20:29:56 发布

VioL1n.-

最新推荐文章于 2022-12-06 20:29:56 发布

阅读量207

点赞数 1

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/m0_57234353/article/details/125655669

版权

表: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| salary | int |
+-------------+------+
Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。

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

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N:=N-1;
  #查询第N高薪水要跳过N-1个数据
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
        IFNULL(
            (SELECT DISTINCT salary 
            FROM Employee
            ORDER BY salary DESC
            LIMIT N,1),NULL
        )
      as getNthHighestSalary
  );
END

示例 1:

输入:
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
n = 2
输出:
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
示例 2:

输入:
Employee 表:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
+----+--------+
n = 2
输出:
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null |
+------------------------+