【leetcode Database】177. Nth Highest Salary

最新推荐文章于 2022-04-27 08:28:40 发布

酱油何在

最新推荐文章于 2022-04-27 08:28:40 发布

阅读量4.4k

点赞数

分类专栏： mysql leetcode解题笔记文章标签： leetcode mysql 数据库

本文链接：https://blog.csdn.net/Kevin_zhai/article/details/52121655

版权

leetcode解题笔记同时被 2 个专栏收录

40 篇文章 3 订阅

订阅专栏

mysql

17 篇文章 0 订阅

订阅专栏

题目：

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.

解析：

把所有salary去重后排序，然后通过limit方法取出第n大的数。代码如下：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
      DECLARE N1 INT;
      SET N1 = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT Salary FROM (SELECT DISTINCT Salary FROM Employee) e ORDER BY Salary DESC LIMIT N1,1
  );
END

注意：limit后面一定不能写N-1，因为limit后面不能跟表达式。