题目
Employee 表:
Column Name | Type |
---|---|
id | int |
salary | int |
id 是这个表的主键。
表的每一行包含员工的工资信息。
编写一个 SQL 查询,获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水,查询应该返回 null 。
查询结果如下例所示。
示例 1:
输入:
Employee 表:
id | salary |
---|---|
1 | 100 |
2 | 200 |
3 | 300 |
输出:
SecondHighestSalary |
---|
200 |
示例 2:
输入:
Employee 表:
id | salary |
---|---|
1 | 100 |
输出:
SecondHighestSalary |
---|
null |
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/second-highest-salary
不同方案
方案一
-- IFNULL函数,LIMIT m,n
SELECT
IFNULL((SELECT DISTINCT
salary
FROM
employee
ORDER BY salary DESC
LIMIT 1,1), NULL) AS secondhighestsalary
;
方案二
-- select() 若为空,默认返回null
SELECT
(
SELECT DISTINCT
salary
FROM
employee
ORDER BY salary DESC
LIMIT 1,1
) AS secondhighestsalary
;
方案三
-- 开窗函数 + union all
SELECT
salary secondhighestsalary
FROM
(
SELECT
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) num
FROM
employee
UNION ALL
SELECT
NULL,
2
) t
WHERE
num = 2
LIMIT 1
;
``````sql
方案四
-- select()+开窗函数
SELECT
(SELECT
salary
FROM
(
SELECT
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) num
FROM
employee
) a
WHERE
a.num = 2
LIMIT 1) AS secondhighestsalary
;