1.求员工薪水
需求一:获取 Employee 表中第二高的薪水(Salary),如果不存在第二高的薪水,那么查询应返回 null。
结果:
SecondHighestSalary |
---|
200 |
建表语句:
Create table If Not Exists Employee (Id int, Salary int);
Truncate table Employee;
insert into Employee (Id, Salary) values (1, 100);
insert into Employee (Id, Salary) values (2, 200);
insert into Employee (Id, Salary) values (3, 300);
方法1:
select
IFNULL((select distinct Salary
from Employeedesc
order by Salary desc
limit 1,1),null) as SecondaryHighestSalary;
#思路:按薪水倒叙排列,然后通过 limit 获取排名第二的薪水。
方法2:
select
max(Salary)
from Employee
where Salary < ( select max(salary) from Employee );
#思路:获取最高薪水,然后获取比最高薪水小的最大薪水即为第二高的薪水。
方法3:
select
max(e1.Salary) as SecondaryHighestSalary
from
Employee e1,
Employee e2
group by
e1.id
having sum(if(e1.Salary>e2.Salary,1,0)) = 1;
#思路:自关联形成笛卡尔积,按照e1的id进行分组,然后将e1当前行的元素与e2的每行元素进行比较,按需求筛选出符合的数据。最大值 > n-1
需求二:获取 Employee 表中第 n 高的薪水(Salary)。
方法:自定义函数。
CREATE FUNCTION getNthHighestSalary_1(N INT) RETURNS INT
BEGIN
SET n = N-1;
RETURN (
SELECT DISTINCT Salary FROM 2_Employee ORDER BY Salary DESC LIMIT n,1
);
END;
select getNthHighestSalary_1(2) ;