[LeetCode-DATABASE] 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  |
+------------+----------+--------+

题意：找到每个部门工资最高的三位员工

思路：首先可以再每个部门内，找到每个员工工资的排位Rank(数值表示在该部门比该员工工资高的工资的数量)值，同时为了方便后面的连接，找到如下的表格：

 

+-------+---------+--------------+------+
|  Name | Salary  | DepartmentId | Rank |
+-------+---------+--------------+------+
| Joe   |  70000  | 1            | 2    |
| Henry |  80000  | 2            | 0    |
| Sam   |  60000  | 2            | 1    |
| Max   |  90000  | 1            | 0    |
| Janet |  69000  | 1            | 3    |
| Randy |  85000  | 1            | 1    |
+-------+---------+--------------+------+

SQL：

SELECT d.Name AS Department, t.Name AS Employee, t.Salary
FROM Department d,
    (
     SELECT e1.Name, e1.Salary, e1.DepartmentId, COUNT(DISTINCT e2.salary) AS Rank
     FROM Employee e1 LEFT JOIN Employee e2
     ON e1.DepartmentId = e2.DepartmentId
     AND e1.salary < e2.Salary
     GROUP BY e1.Id
     ) t
WHERE d.Id = t.DepartmentId
AND t.Rank < 3
ORDER BY d.Id ASC, t.Salary DESC;

 

由于其中的t表格，只是Employee和自己内连接，因此可以相应的简化为：

SELECT D.Name AS Department, E.Name AS Employee, E.Salary AS Salary
FROM Employee E, Department D
WHERE (SELECT COUNT(DISTINCT(Salary)) FROM Employee
      WHERE DepartmentId = E.DepartmentId AND Salary > E.Salary) < 3
AND E.DepartmentId = D.Id
ORDER by E.DepartmentId, E.Salary DESC;