LeetCode177. 第N高的薪水解题思路及引申的排序函数用法

前言

重刷leetcode数据库题目的时候，刷到求第N大数据的题目，用到排序相关的函数，但这块有些遗忘了。看了部分博主的文后，略有所思，在此总结下。

一、leetcode第N大薪水的题目及解题思路

1. 题目：

2. 解题思路

(1) 使用dense_rank()函数进行排序并去重

select 
	distinct salary
	,dense_rank() over (order by salary desc) rank 
from Employee;

psl：这里distinct 意义是当出现多条数据并列时，仅取出一个数据，因为后面要给数值变量result存起来

（2）将查询结果作为表，传入 N 值再次进行查询，并且使用nvl 函数返回 不存在时的null

select nvl(salary,null) 
from (
        select distinct salary,dense_rank() over (order by salary desc) rank 
        from Employee
    )
where rank = N;

(3) 完整代码

CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS
result NUMBER;
BEGIN
    /* Write your PL/SQL query statement below */
    select 
    	nvl(salary,null)  into result  
    from (select distinct salary, dense_rank()over(order by salary DESC) AS RNUM FROM Employee)  
    WHERE RNUM = N
;
    RETURN result;
END;

psl：into result，把结果写进result中，最终返回这个变量值

二、rownum、row_number()、rank()、dense_rank() 函数的用法详解

1. 适用场景

• 取前N条数据
• 取最大值/最小值
• 取第N大的数据/排名第N的数据 -> 取各个模块中的排名前N的员工

2. 核心差异：

• rownum是先生成序号再排序。
• row_number()是先排序，再生成序号，按序号递增生成，序号不同；
• rank()同一个值当成一块，序号一样，下一个块序号是前面所有行+1；
• dense_rank()同一个值当成一块，序号一样，下一个块序号是块序号+1；
  

3. row_number()函数

• 语法：row_number() over([partition by col1] order by col2 [ASC | DESC] [,col3 [ASC | DESC]]…)，row_number()必须和over()绑定使用，不可单独使用。
• 基本用法

SELECT row_number()over(order by empno) AS rnum, t1.* FROM emp t1;

psl：结果并列时，结果会随机先后排序。举例：若empno相同，则结果会随机先后排序；比如empno为1933，Ename为KK；empno为1933，Ename为CC两条数据，那么最终序号是两条随机先后排序，可能是序号1，KK，也有可能序号1，CC。

• 使用row_number()分页

SELECT * FROM (
  SELECT row_number() over(order by empno) AS rnum, t1.* FROM emp t1
) t WHERE t.rnum BETWEEN 4 AND 6;

• 使用partition by参数分区生成序号

SELECT
    row_number() over(partition by deptno order by empno) AS rnum
    ,t1.*
FROM emp t1;

4. rank()与dense_rank()函数

1. rank()与dense_rank()核心区别：第二个不同排序值的序号值（行数+1 or 块+1）

• rank()：排序值相同的为一个序号（此称为块），第二个不同排序值的序号为前面所有行的值+1

SELECT rank()over(order by job) AS rnum, job ,ename FROM emp t1;

• dense_rank()函数：排序值相同的为一个序号（此称为块），第二个不同排序值的序号为前面所有行的值+1

SELECT dense_rank()over(order by job) AS rnum, job ,ename FROM emp t1;

• 加上partition by参数：指定字段分组后，再组内排序
  

SELECT dense_rank()over(partition by deptno order by job)rnum,job,ename,deptno
FROM emp t1;

5. 总结

1. 取前多少条记录，使用rownum伪列。注意rownum不能用于排序并过滤的场合。
2. 取多少条到多少条的记录（分页），使用row_number()函数。例如：查出员工编号排序后为4 ~ 6条的记录。
3. 取某个组别中最大值记录或最小值的记录，使用row_number()函数/rank()函数，并结合partition by参数。例如：查出每个部门工资最高的员工。
4. 取某个组别中并列排名第几记录，使用dense_rank()函数，并结合partition by参数。例如：查出各部门工资排名第三的所有员工。