SQL实现LeetCode(185.系里前三高薪水)

[leetcode] 185.department top three salaries 系里前三高薪水

the employee table holds all employees. every employee has an id, and there is also a column for the department id.

+—-+——-+——–+————–+
| id | name  | salary | departmentid |
+—-+——-+——–+————–+
| 1  | joe   | 70000  | 1            |
| 2  | henry | 80000  | 2            |
| 3  | sam   | 60000  | 2            |
| 4  | max   | 90000  | 1            |
| 5  | janet | 69000  | 1            |
| 6  | randy | 85000  | 1            |
+—-+——-+——–+————–+

the department table holds all departments of the company.

+—-+———-+
| id | name     |
+—-+———-+
| 1  | it       |
| 2  | sales    |
+—-+———-+

write a sql query to find employees who earn the top three salaries in each of the department. for the above tables, your sql query should return the following rows.

+————+———-+——–+
| department | employee | salary |
+————+———-+——–+
| it         | max      | 90000  |
| it         | randy    | 85000  |
| it         | joe      | 70000  |
| sales      | henry    | 80000  |
| sales      | sam      | 60000  |
+————+———-+——–+

这道题是之前那道department highest salary的拓展，难度标记为hard，还是蛮有难度的一道题，综合了前面很多题的知识点，首先看使用select count(distinct)的方法，我们内交employee和department两张表，然后我们找出比当前薪水高的最多只能有两个，那么前三高的都能被取出来了，参见代码如下：

解法一：

select d.name as department, e.name as employee, e.salary from employee e
join department d on e.departmentid = d.id
where (select count(distinct salary) from employee where salary > e.salary
and departmentid = d.id) < 3 order by d.name, e.salary desc;

下面这种方法将上面方法中的<3换成了in (0, 1, 2)，是一样的效果：

解法二：

select d.name as department, e.name as employee, e.salary from employee e, department d
where (select count(distinct salary) from employee where salary > e.salary
and departmentid = d.id) in (0, 1, 2) and e.departmentid = d.id order by d.name, e.salary desc;

或者我们也可以使用group by having count(distinct ..) 关键字来做：

解法三：

select d.name as department, e.name as employee, e.salary from 
(select e1.name, e1.salary, e1.departmentid from employee e1 join employee e2 
on e1.departmentid = e2.departmentid and e1.salary <= e2.salary group by e1.id 
having count(distinct e2.salary) <= 3) e join department d on e.departmentid = d.id 
order by d.name, e.salary desc;

下面这种方法略微复杂一些，用到了变量，跟consecutive numbers中的解法三使用的方法一样，目的是为了给每个人都按照薪水的高低增加一个rank，最后返回rank值小于等于3的项即可，参见代码如下：

解法四：

select d.name as department, e.name as employee, e.salary from 
(select name, salary, departmentid,
@rank := if(@pre_d = departmentid, @rank + (@pre_s <> salary), 1) as rank,
@pre_d := departmentid, @pre_s := salary 
from employee, (select @pre_d := -1, @pre_s := -1, @rank := 1) as init
order by departmentid, salary desc) e join department d on e.departmentid = d.id
where e.rank <= 3 order by d.name, e.salary desc;

类似题目：

department highest salary

second highest salary

combine two tables

参考资料：

到此这篇关于sql实现leetcode(185.系里前三高薪水)的文章就介绍到这了,更多相关sql实现系里前三高薪水内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！