Employee
表:
+-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ 在 SQL 中,id 是这个表的主键。 表的每一行包含员工的工资信息。
查询并返回 Employee
表中第二高的薪水 。如果不存在第二高的薪水,查询应该返回 null(Pandas 则返回 None)
。
查询结果如下例所示。
示例 1:
输入: Employee 表: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ 输出: +---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+
示例 2:
输入: Employee 表: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | +----+--------+ 输出: +---------------------+ | SecondHighestSalary | +---------------------+ | null | +---------------------+
方法一:(知识点:窗口函数,ifnull(值,null))
select ifnull(
(select
salary
from
(select
id,salary,
dense_rank() over(order by salary desc) as rnk
from
Employee)tmp
where rnk=2 limit 1),null)
as SecondHighestSalary
方法二:(先剔除最大值,再找最大值)
select
max(salary) as SecondHighestSalary
from Employee
where salary
not in (select max(salary) from Employee)