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实现系里前三高薪水内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！