SQL 177. Nth Highest Salary

最新推荐文章于 2024-07-25 21:03:55 发布

weixin_49156016

最新推荐文章于 2024-07-25 21:03:55 发布

阅读量71

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_49156016/article/details/127492818

版权

知识点：

1. Window function

2. Offset

方法1：offset

来源：https://www.youtube.com/watch?v=ms-99n1KbT0

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN

SET N = N-1;
  RETURN (
select
      distinct salary 
from employee
order by salary desc
limit 1 offset n
   
  );
END


# n means skip n rows, take one after that
# so if we want top 3, it will skip 3rd and return 4th
# therefore, we need to use n-1
# distinct 的作用是如果有相同的salary 100, 100, 200, 那么会选取第二高的

方法2：window function

使用Dense_rank而非rank的原因：

value 3, 4, 5, 5, 7

dense_rank 1, 2, 3, 3, 4

rank 1, 2, 3, 3, 5

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (

      select distinct(salary) 
      from 
      (select salary,dense_rank() over (order by salary desc) as ds from employee) as temp     
      where ds=N
      
  );
END

此处不用row_number()的原因：

如果数据如下

+----+--------+
 | id   | salary   |
+----+--------+
 | 1     | 100     |
 | 2     | 200     |
 | 3     | 300     |
 | 4     | 300     |
+----+--------+

使用row_number() -- 连续数据且每个数据都是unique的

+----+--------+----+
 | id   | salary   | rn   |
+----+--------+----+
 | 4     | 300     | 1     |
 | 3     | 300     | 2     |
 | 2     | 200     | 3     |
 | 1     | 100     | 4     |
+----+--------+----+

如果要爬取get 2nd highest salary，现在的rn=2并非是第二高的薪资，只是table的第二行。

所以需要用dense_rank 来实现：

+----+--------+----+
 | id   | salary   | rn   |
+----+--------+----+
 | 4     | 300     | 1     |
 | 3     | 300     | 1     |
 | 2     | 200     | 2     |
 | 1     | 100     | 3     |
+----+--------+----+

也没法用rank如下，你无法得到2nd highest salary除非distinct salary first再用rank

+----+--------+----+
 | id   | salary   | rn   |
+----+--------+----+
 | 4     | 300     | 1     |
 | 3     | 300     | 1     |
 | 2     | 200     | 3     |
 | 1     | 100     | 4     |
+----+--------+----+