185. Department Top Three Salaries (hard)

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  |
+------------+----------+--------+

题目的要求很简单, 就是求每个部门薪资排名前三的雇员资料,但是实际,,,唉

之前一直用mysql做题,今天做了几道题发现sql server更强,特别是窗口函数,mysql8.0也开始支持窗口函数,总之用了排名函数后问题就非常好解决了

SELECT d.Name Department, t.Name Employee, t.Salary
FROM (SELECT Name, Salary, DepartmentId, dense_rank() OVER(partition by DepartmentId order by Salary DESC) dk FROM Employee) t JOIN Department d
ON t.DepartmentId = d.Id
WHERE t.dk <= 3;