想要精通算法和SQL的成长之路 - 第N高的薪水（SQL）

前言

想要精通算法和SQL的成长之路 - 系列导航

一. 第二高的薪水

原题链接
编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null 。

我们来建立一张表：

随便插入几条数据：

那么首先，查询薪水的最高值，我们就利用到了 max 函数：

SELECT max(salary) as secondhighestsalary  from Employee 

那么查询第二高的怎么办？简而言之就是

• 排除掉最大值，再查一次max，得到的结果就是第二高的。

SELECT
	max( salary ) AS secondhighestsalary 
FROM
	Employee 
WHERE
	salary != (SELECT max( salary ) FROM Employee)

1.1 offset 的使用

offset n 的意思代表跳过n条语句。一般和limit一起用。例如：limit n offset m 代表：先跳过m条结果，再取前n条结果。

我们先看下这个SQL

select salary from Employee order by salary desc limit 2 

结果如下：

因此我们需要考虑到去重。加一个distinct字段。

此时我们再通过offset来取第二个值，并赋个别名即可：

SELECT
	( SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1 ) AS secondhighestsalary

大家在来思考下，为什么要下面这条SQL不行？

select distinct salary as secondhighestsalary from Employee order by salary desc limit 1 offset 1

如果表里面只有一条数据的话，它的执行效果如下：

不满足返回null的要求。 这么一看，第一个SQL就相当于 select (空数据) as secondhighestsalary 这里就会将对应的字段设置为null。因此得用第一种。

1.2 ifnull 的使用

如果第一个表达式为null，就返回第二个参数的值。否则返回第一个表达式的返回结果。

IFNULL(expression, alt_value)

结果如下：

SELECT
	ifnull( ( SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1 ), NULL ) AS secondhighestsalary

1.3 分页查询

简单利用分页即可，limit从0开始，那么我们限制每页的大小是1，取第二页的即是第二大的。

SELECT
	ifnull( ( SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1, 1 ), NULL ) AS secondhighestsalary

1.3 总结

四种方式：

# 第一种，select + where
SELECT
	max( salary ) AS secondhighestsalary 
FROM
	Employee 
WHERE
	salary != (SELECT max( salary ) FROM Employee)

# 第二种 limit 1 offset 1
SELECT ( SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1 ) AS secondhighestsalary

# 第三种 limit + ifnull
SELECT ifnull( ( SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1 ), NULL ) AS secondhighestsalary

# 第四种 分页
SELECT ifnull( ( SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1, 1 ), NULL ) AS secondhighestsalary

知识点：

• ifnull的使用。
• offset的使用：limit n offset m 代表：先跳过m条结果，再取前n条结果。

二. 第N高的薪水

原题链接
相当于第一个题目的进阶了，直接结果如下：相当于把上面的1替换成N-1。

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