## 题目描述：

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

## 解题思路：

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

## 易错样例：

```{"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
``````

``````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;
``````

Pingbacks已关闭。

1. 郑州SEO优化 发布于 2015年1月27日 09:43 #

博主博客的百度竞价广告收入如何啊？

2. yn 发布于 2015年3月16日 09:33 #

不太理解最后一个简单解法。。为什么count distinct salary 可以有不同的值？难道返回的不是就一个总值吗？求解！

3. 在线疯狂 发布于 2015年3月16日 16:52 #

count distinct salary在where子句中，会对Employee E, Department D等值连接产生的每一条记录做筛选。