每日leetcode-185

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 | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 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 (order of rows does not matter).

+————+———-+——–+
| Department | Employee | Salary |
+————+———-+——–+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+————+———-+——–+
Explanation:

In IT department, Max earns the highest salary, both Randy and Joe earn the second highest salary, and Will earns the third highest salary. There are only two employees in the Sales department, Henry earns the highest salary while Sam earns the second highest salary.

这个里面最重要的是怎么能够选出满足条件top three salaries in each of the department的。这种前几个的问题就用选择之后count比较的方法就可以，其实就是说每个选中的人，和他同一部门并且比他工资高的工资（要去重）小于3个。

所以：

# Write your MySQL query statement below
select t2.Name as Department, t1.Name as Employee, Salary
from Employee t1 join Department t2 on t1.DepartmentId = t2.Id
where
    (select count(distinct e.Salary) from Employee e where e.Salary > t1.Salary and e.DepartmentId = t1.DepartmentId) < 3
order by Department, Salary desc;