SQL-leetcode刷题笔记-177第n高的薪水

题目描述

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

表1：Employee (Primary Key: Id)

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

相关知识

1. 存储函数

   • 存储函数(function)和存储过程(procedure)都被称为存储例程(routine)，两者的主要区别是函数有一个返回值，并且可以在SELECT语句中调用

   • 所有代码要写在BEGIN和END块之间

   • 由于存储例程的语句应以分隔符;结尾，因此必须更改Mysql的分隔符，以便Mysql不会用正常语句解释存储例程中的SQL语句，创建过程结束后，可以将分隔符改回默认值。

   • 语法格式

     CREATE FUNCTION function_name(value_name value_type) RETURNS return_type
     BEGIN
     	-- 声明变量
     	DECLARE value1_name value1_type;
         DECLARE value2_name value2_type;
         DECLARE value3_name value3_type;
              
         -- 查询并把结果存入变量
         SELECT ... INTO value1_name FROM .... WHERE ...;
              
         -- 变量赋值
         SET value2_name = value1_name + 1;
              
         -- 逻辑运算
         IF value2_name < 6666 THEN
         SET value3_name ='2333';
         ELSEIF (value2_name > 8888 AND value2_name < 10000) THEN
         SET value3_name='9999';
         ELSE
         SET value3_name='zero';    
              
         -- 返回运算结果
         RETURN (value3_name);
     END
     

解题过程

1. leetcode给出的解题模板是函数，这是因为每次调用是要查询第N高的工资，而不是固定的，而且返回字段名包含N，没办法固定。

   CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
   BEGIN
     RETURN (
         # Write your MySQL query statement below.
            
     );
   END
   

2. 根据175.查询第二高的工资的解题经验，在RETURN中直接写sql，执行报错

   • sql

     CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
     BEGIN
       RETURN (
       	SELECT IFNULL(
               (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT N-1,1),
               NULL
           )      
       );
     END
     

   • 查询后发现LIMIT只能接收已知结果的值，不能嵌套表达式或子查询

3. 直接定义在RETURN语句前，定义变量，完成序号从0开始这个操作，即完成减一的操作

   • sql

     CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
     BEGIN
       DECLARE M INT;
       SET M=N-1;
       RETURN (
           SELECT IFNULL(
               (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M,1),
               NULL
           )
       );
     END
     

   • 提交，答案正确

4. 发现其他人使用的消耗时间比我的小

   • 有人没有用变量，在RETURN依据中完成了功能

     CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
     BEGIN
       RETURN (     
       SELECT  IF(count<N,NULL,min) 
       FROM
         (SELECT MIN(Salary) AS min, COUNT(1) AS count
         FROM
           (SELECT DISTINCT Salary
           FROM Employee ORDER BY Salary DESC LIMIT N) AS a
         ) as b
       );
     END
     

     • 首先排序获得前N大的派生表
     • COUNT(1)的作用相当于COUNT(*)，统计条目数，因为可能不够N个
     • MIN()函数求出了Salary的最小值，即第N大的Salary
     • 最外层使用IF()完成了是否为空的判断

   • 如下代码并没有使用IFNULL()，这是因为函数执行后没有结果，自动返回NULL，而不是空

     CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
     BEGIN
         SET N=N-1;
         RETURN (
         SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT N,1
         );
     END
     

学习总结

1. 存储函数返回值中字段名为函数名(参数1,......,参数n)
2. COUNT(1)并不是返回1，它的作用和效率相当于COUNT(*)，就是统计元组的个数
3. LIMIT接受的参数必须是已知值，不能是表达式或子查询。如果必须变化，那只能声明一个变量，在之前的语句中完成计算。
4. 存储函数当计算结果为空时，会自动返回NULL，而无需进行IFNULL()判断。这也同时意味着调用存储函数有可能返回NULL，需要对这种情况进行考虑，避免因此而出错。