Second Highest Salary

发表于   |   分类于 ， ，

Write a SQL query to get the second highest salary from the Employee table.

Id Salary
1 100
2 200
3 300

For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return null.

描述

查找 Salary 第二大的数，若不存在，则返回 null

代码

1
2
3
4
5
6
7
8
9
-- 方法一
-- 使用 max()，当不存在时，返回 null

select 
	max(Salary) as SecondHighestSalary 
from 
	Employee 
where 
	Salary < (select max(Salary) from Employee);
1
2
3
4
5
6
7
8
9
-- 方法二
-- limit 的第一个参数代表偏移量（从 0 开始），第二个参数代表读取的行数
-- 此方法，通过修改第一个参数，可以获取任意第 n 大的数

select 
	(
	select distinct Salary from Employee order by Salary desc limit 1,1
	) 
as SecondHighestSalary;
1
2
3
4
5
6
7
8
9
10
11
-- 方法三
-- 此方法有缺陷，当 Employee 为空表时，无法得到 null
-- union 将多个不同的 select 语句得到的结果合并到一个结果集中

select 
	distinct Salary as SecondHighestSalary 
from 
	Employee 
union select null 
order by SecondHighestSalary desc 
limit 1,1;
1
2
3
4
5
6
7
8
9
10
11
12
-- 方法四
-- 通过更改数字，可以获取任意第 n 大的数

select 
	(
	select 
		distinct Salary 
	from 
		Employee e 
	where 2 = (select count(distinct Salary) from Employee where Salary >= e.Salary)
	) 
as SecondHighestSalary;

延伸

limit 的偏移量

mysql：证明为什么用 limit 时，offset 很大会影响性能

test 表

id val source
1 4 4 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 方法一
select * from test where val = 4 limit 300000,5;


-- 方法二
select * from test a inner join 
	(
	select id from test where val = 4 limit 300000,5
	) b 
on a.id = b.id;

-- 方法二的效率明显高于方法一
-- 主要由于方法二的子查询是根据 id 索引快速的找出需要的 5 条数据，然后再查找其他内容
-- 而方法一则是 select * ，查找的数据包含不是索引的内容，无法直接得到，需要间接查询，耗时