MySQL第N高的薪水

素颜马尾好姑娘i 2023-10-06 17:24 16阅读 0赞

## MySQL第N高的薪水 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70]

## MySQL通用查询策略 ##

排名是数据库中的一个经典题目，实际上又根据排名的具体细节可分为3种场景：

*  连续排名，例如薪水3000、2000、2000、1000排名结果为1-2-3-4，体现同薪不同名，排名类似于编号
 *  同薪同名但总排名不连续，例如同样的薪水分布，排名结果为1-2-2-4
 *  同薪同名且总排名连续，同样的薪水排名结果为1-2-2-3

不同的应用场景可能需要不同的排名结果，也意味着不同的查询策略。本题的目标是实现第三种排名方式下的第N个结果，且是全局排名，不存在分组的问题，实际上还要相对简单一些。

值得一提的是：在Oracle等数据库中有窗口函数，可非常容易实现这些需求，而MySQL直到8.0版本也引入相关函数。最新OJ环境已更新至8.0版本，可直接使用窗口函数。

### 思路1：单表查询 ###

由于本题不存在分组排序，只需返回全局第N高的一个，所以自然想到的想法是用order by排序加limit限制得到。需要注意两个细节：

1、同薪同名且不跳级的问题，解决办法是用group by按薪水分组后再order by  
2、排名第N高意味着要跳过N-1个薪水，由于无法直接用limit N-1，所以需先在函数开头处理N为N=N-1。

> 注：这里不能直接用limit N-1是因为limit和offset字段后面只接受正整数（意味着0、负数、小数都不行）或者单一变量（意味着不能用表达式），也就是说想取一条，limit 2-1、limit 1.1这类的写法都是报错的。

注：这种解法形式最为简洁直观，但仅适用于查询全局排名问题，如果要求各分组的每个第N名，则该方法不适用；而且也不能处理存在重复值的情况。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
        SET N := N-1;
      RETURN (
          # Write your MySQL query statement below.
          SELECT 
                salary
          FROM 
                employee
          GROUP BY 
                salary
          ORDER BY 
                salary DESC
          LIMIT N, 1
      );
    END

查询效率

![在这里插入图片描述][20210420152147167.png]

### 思路2：子查询 ###

*  排名第N的薪水意味着该表中存在N-1个比其更高的薪水
 *  注意这里的N-1个更高的薪水是指去重后的N-1个，实际对应人数可能不止N-1个
 *  最后返回的薪水也应该去重，因为可能不止一个薪水排名第N
 *  由于对于每个薪水的where条件都要执行一遍子查询，注定其效率低下

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
      RETURN (
          # Write your MySQL query statement below.
          SELECT 
              DISTINCT e.salary
          FROM 
              employee e
          WHERE 
              (SELECT count(DISTINCT salary) FROM employee WHERE salary>e.salary) = N-1
      );
    END

查询效率

![在这里插入图片描述][20210420152359698.png]

### 思路3：自连接 ###

一般来说，能用子查询解决的问题也能用连接解决。具体到本题：

*  两表自连接，连接条件设定为表1的salary小于表2的salary
 *  以表1的salary分组，统计表1中每个salary分组后对应表2中salary唯一值个数，即去重
 *  限定步骤2中having 计数个数为N-1，即实现了该分组中表1salary排名为第N个
 *  考虑N=1的特殊情形(特殊是因为N-1=0，计数要求为0)，此时不存在满足条件的记录数，但仍需返回结果，所以连接用left join
 *  如果仅查询薪水这一项值，那么不用left join当然也是可以的，只需把连接条件放宽至小于等于、同时查询个数设置为N即可。因为连接条件含等号，所以一定不为空，用join即可。  
    注：个人认为无需考虑N<=0的情形，毕竟无实际意义。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
      RETURN (
          # Write your MySQL query statement below.
          SELECT 
              e1.salary
          FROM 
              employee e1 JOIN employee e2 ON e1.salary <= e2.salary
          GROUP BY 
              e1.salary
          HAVING 
              count(DISTINCT e2.salary) = N
      );
    END
    
    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
      RETURN (
          # Write your MySQL query statement below.
          SELECT 
              DISTINCT e1.salary
          FROM 
              employee e1 LEFT JOIN employee e2 ON e1.salary < e2.salary
          GROUP BY 
              e1.salary
          HAVING 
              count(DISTINCT e2.salary) = N-1
      );
    END

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210420150507913.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWd1YW5nMTAy,size_16,color_FFFFFF,t_70
[20210420152147167.png]: https://img-blog.csdnimg.cn/20210420152147167.png
[20210420152359698.png]: https://img-blog.csdnimg.cn/20210420152359698.png