[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！