【MYSQL】第N高薪水实现方式

Aubye

于 2023-01-09 16:46:22 发布

阅读量206

点赞数

分类专栏： SQL 文章标签： mysql 数据库

原文链接：https://leetcode.cn/problems/nth-highest-salary/solution/mysql-zi-ding-yi-bian-liang-by-luanz/

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1 题目介绍

来自于力扣177题，第N高薪水
原文https://leetcode.cn/problems/nth-highest-salary/solution/mysql-zi-ding-yi-bian-liang-by-luanz/

在这里插入图片描述

这个问题是比较一般化的问法，如果是特殊化的问法，比如第2高薪水，则可以直接通过子查询排除最高薪水后选取最高值。对于第N高的薪水，需要将代码一般化，有以下的解决方式：

2 问题分析

排名是数据库中的一个经典题目，实际上又根据排名的具体细节可分为3种场景：不同的应用场景可能需要不同的排名结果，也意味着不同的查询策略。本题的目标是实现第三种排名方式下的第N个结果，且是全局排名，不存在分组的问题，实际上还要相对简单一些。

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

2.1 单表查询

同薪同名且不跳级的问题，解决办法是用group by按薪水分组后再order by
排名第N高意味着要跳过N-1个薪水，由于无法直接用limit N-1，所以需先在函数开头重新设置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 #limit有两个参数，第一个参数表示从第几行数据开始查，第二个参数表示查几条数据
      #这里表示从第N-1个开始查询，查一条数据，也就是第N个数据
  );
END

在这里插入图片描述

2.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

这里相当于进行了多个子查询，在where里面进行筛选，直到有N-1个子查询里面薪资都大于e这个子查询中的薪资（利用count实现，应该去重，因为可能有多个大于N的薪资水平），则此时e中剩下的就是第N高的薪资水平。
在这里插入图片描述

2.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 
          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

原理仍然是排除N-1高的部分，从而筛选出第N高的水平，使用的是左连接，因为如果N=1的话没有记录，但是需要返回结果，左连接的情况下e1还是有结果的；
这里having的用法就是先通过sql语句把所有数据查询出来，再用 group by 进行分组，然后把分完组的数据用聚合函数进行统计，只不过查询语句和聚合函数之间需要用having连接；（group by 、having、聚合函数通常一起使用）

2.4 笛卡尔积

查询的原理和2和3相同，e2里面是前N个最大的

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