题目描述：

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

题目大意：

雇员表Employee保存了雇员的Id，姓名，薪水，以及部门Id。部门表Department保存了部门的Id和名称。

编写一个SQL查询找出每一个部门薪水排名前3位的雇员信息（排名可以并列），样例如上。

解题思路：

参考StackOverflow的问答（http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results）

LeetCode OJ将此题的难度标记为Hard，可见题目的确有一定的难度。

解题步骤：

首先将雇员表按照雇员的DepartmentId和Salary分别正序和倒序排列；

然后利用MySQL用户定义变量对雇员标记排名rank值，同一部门的rank值从1开始，按照薪水降序递增（注意薪水相同时rank值不变）；

最后筛选rank值不大于3的雇员信息，并与Department表做关联即可。

易错样例：

{"headers": {"Employee": ["Id", "Name", "Salary", "DepartmentId"], "Department": ["Id", "Name"]}, "rows": {"Employee": [[1, "Joe", 60000, 1], [2, "Ralph", 50000, 1], [3, "Joel", 60000, 1], [4, "Tracy", 75000, 1]], "Department": [[1, "IT"]]}}
Output:    {"headers": ["Department", "Employee", "Salary"], "values": [["IT", "Tracy", 75000], ["IT", "Joe", 60000], ["IT", "Joel", 60000]]}
Expected:    {"headers": ["Department", "Employee", "Salary"], "values": [["IT", "Tracy", 75000], ["IT", "Joe", 60000], ["IT", "Joel", 60000], ["IT", "Ralph", 50000]]}

SQL语句：

SELECT d.NAME AS Department, t.NAME AS Employee, Salary FROM (
  SELECT    DepartmentId,
            NAME,
            Salary, 
            @rank := IF(@prevDeptId != DepartmentId, 1, 
                         IF(@prevSalary = Salary, @rank, @rank + 1) ) AS Rank,
            @prevDeptId := DepartmentId AS prevDeptId,
            @prevSalary := Salary AS prevSalary
  FROM      Employee e, (SELECT @rank := 0, @prevDeptId := NULL, @prevSalary := NULL) r
  ORDER BY  DepartmentId ASC, Salary DESC
) t INNER JOIN Department d ON t.DepartmentId = d.ID
WHERE t.rank <= 3

另外，在LeetCode Discuss中看到这样一个短小精悍的解法：

链接：https://oj.leetcode.com/discuss/23002/my-tidy-solution

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;

 

本文链接：http://bookshadow.com/weblog/2015/01/24/leetcode-department-top-three-salaries/
请尊重作者的劳动成果，转载请注明出处！书影博客保留对文章的所有权利。

如果您喜欢这篇博文，欢迎您捐赠书影博客： ，查看支付宝二维码