想要精通算法和SQL的成长之路 - 第N高的薪水(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