1.求员工薪水

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) ;
上一篇:MySQL基础(二)


下一篇:leetcode-mysql 2021-06-02