leetcode176 第二高的薪水 Second Highest Salary
编写一个 SQL 查询,获取 Employee
表中第二高的薪水(Salary) 。
创建表和数据:
drop table Employee;
Create table If Not Exists Employee (Idint, Salary int);
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.表自连接
用表的自连接,构造偏序关系。再找次序的最大值,就一定是第二高的薪水。同时,max在没有元组输入时,会返回NULL。如在表中的元组少于2个时。
SELECT MAX(e1.salary) as SecondHighestSalary FROM Employee e1 JOIN Employee e2 WHERE e1.salary < e2.salary;
2.子查询
子查询方法。用子查询找出最大值,再排除最大值的结果中,求最大值。必须要借助于MAX函数。
select max(salary) as SecondHighestSalary from Employee where salary !=(select max(salary) from Employee);
select max(E.Salary) as SecondHighestSalary from Employee as E where E.Salary < ( select max(Salary) from Employee
);
select max(E1.Salary) as SecondHighestSalary from Employee as E1 where 1 = ( select count(*) from Employee as E2 where E1.Salary < E2.Salary );
不用MAX函数的方法
SELECT (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1) AS SecondHighestSalary;