LeetCode：Database 02.求第N高的薪水

最新推荐文章于 2022-05-15 15:54:15 发布

Xiao Miao

最新推荐文章于 2022-05-15 15:54:15 发布

阅读量704

点赞数 1

分类专栏： LeetCode 文章标签： mysql

本文链接：https://blog.csdn.net/qq_43713049/article/details/116276728

版权

LeetCode 专栏收录该内容

115 篇文章 1 订阅

订阅专栏

本文介绍了如何使用MySQL中的窗口函数和自定义函数来编写SQL查询，以获取Employee表中指定位置的最高薪水。通过实例演示了如何解决在MySQL 8.0版本中创建自定义函数的问题，并提供了解决方案。

摘要由CSDN通过智能技术生成

要求：编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

表Employee：

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

分析：
1.首先需要求第n高的薪水，那么首先需要用到开窗函数dense_rank()进行一个排序
2.为了避免所求到的薪水重复，我们需要distinct去重
3.考虑到求取的结果要起一个别名"getNthHighestSalary(N)"，我最先考虑到的是使用字符串拼接输出结果，但是缺少对应的函数N，后来查阅了下资料发现这里我们可以使用CREATE FUNCTION 语句创建自定义函数，下面是CREATE FUNCTION语句用法：

CREATE FUNCATION <函数名>  ( [ <参数1> <类型1> [ , <参数2> <类型2>] ] … )
RETURNS <类型>
BEGIN
RETURN (sql语句体); 
END

SQL语句:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      select distinct a.sl from(select salary as sl,dense_rank() over(order by salary desc)as r 
      from employee)a where a.r=N
  );
END

在mysql8.0以上版本创建自定义函数会遇到以下问题：
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
因为我们的bin-log日志没有开启

查看bin-log是否开启

show variables like 'log_bin_trust_function_creators';

解决方案：
1.我们需要在数据库中输入以下内容，

set global log_bin_trust_function_creators=1;

2.使用1的方法在计算机重启后失效，为了永久有效，需要在在my.cnf配置文件中添加：

log_bin_trust_function_creators=1

运行：

drop FUNCTION  if exists getNthHighestSalary;
DELIMITER //
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    RETURN (
        select distinct a.sl from(select salary as sl,dense_rank() over(order by salary desc)as r
                                  from employee)a where a.r=N
    );
END//

DELIMITER  ;
SELECT  getNthHighestSalary(2);