SQL 176. 第二高的薪水

---

一、题目

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

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

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/second-highest-salary
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

二、步骤

1.LIMIT 1 OFFSET 1 从第一行开始返回一行

SELECT Salary 
FROM Employee
LIMIT 1 OFFSET 1

但是如果表中只有一条记录，或者薪资只有一个取值时，该SQL语句什么都不返回。但题目中要求返回NULL。

2.改进

（1）将SELECT语句作为子查询
如果该元素不存在，相当于 SELECT NULL
（2) 可能会有重复值，使用DISTINCT降重
（3）先对Salary列降序输出，然后选取第二个输出。

#如果没有第二高的薪资，什么也不返回 ,题目中要求返回null
SELECT (SELECT DISTINCT Salary 
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1) as SecondHighestSalary

3. 排除最大值后的最大值就是第二高的值

SELECT DISTINCT max(salary) AS SecondHighestSalary
FROM Employee
WHERE salary<(SELECT DISTINCT max(salary)
              FROM Employee);

4. 判断空值的函数（ifnull）函数

ifnull(a,b) ：

如果 a 不是空，结果返回a

如果 a 是空，结果返回b

SELECT ifnull((SELECT DISTINCT salary 
              FROM Employee
              ORDER BY salary DESC
              LIMIT 1 OFFSET 1), null)
as SecondHighestSalary;

https://leetcode-cn.com/problems/second-highest-salary/solution/tu-jie-sqlmian-shi-ti-ru-he-cha-zhao-di-ngao-de-sh/

177. 第N高的薪水

(1) LIMIT … OFFSET …
从第N-1行开始的第一行
应该为 LIMIT N-1 OFFSET 1
limit和offset字段后面只接受正整数（意味着0、负数、小数都不行）或者单一变量（不能用表达式）

BEGIN
	declare m int;
	set m=n-1;

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  declare m int;
  set m = n-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT (
          SELECT DISTINCT salary
          FROM Employee
          ORDER BY salary DESC
          LIMIT m OFFSET 1
      )
 );
END

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT ifnull((
          SELECT DISTINCT salary
          FROM Employee
          ORDER BY salary DESC
          LIMIT N OFFSET 1),null)
  );
END

但是结果有问题
当N=1时，返回NULL
(2) 找出不重复的salary，使大于其的不重复的薪资数目为n-1
对于每个薪水的where条件都要执行一遍子查询，效率低下

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

(3) 使用自连接
因为第n个salary可能为空，所以使用LEFT JOIN

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

(4)笛卡尔积法
笛卡尔积：将两个表中的每一个元素进行两两配对

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

（5）内置函数
row_number(): 同薪不同名，相当于行号，例如3000、2000、2000、1000排名后为1、2、3、4
rank(): 同薪同名，有跳级，例如3000、2000、2000、1000排名后为1、2、2、4
dense_rank(): 同薪同名，无跳级，例如3000、2000、2000、1000排名后为1、2、2、3
ntile(): 分桶排名，即首先按桶的个数分出第一二三桶，然后各桶内从1排名，实际不是很常用

这三个函数必须要要与其搭档over()配套使用，
over()中的参数常见的有两个：
partition by，按某字段切分
order by，与常规order by用法一致，也区分ASC(默认)和DESC，因为排名总得有个依据

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
        SELECT 
            DISTINCT salary
        FROM 
            (SELECT 
                salary, dense_rank() over(ORDER BY salary DESC) AS rnk
             FROM 
                employee) tmp
        WHERE rnk = N
  );
END

作者：luanhz
链接：https://leetcode-cn.com/problems/nth-highest-salary/solution/mysql-zi-ding-yi-bian-liang-by-luanz/
来源：力扣（LeetCode） 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。