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

这道题目要找出每个部门薪水前三的员工.

思路如下：

首先找出每个部门第三位的薪水作为临时结果l，然后和Employee,Department两张表做多表连接。

下面是找出临时结果 l 的方法，首先将employee按照departmentid升序，salary降序排列，然后对该表从上往下扫描，设置@rank变量表示该salary在该department中的排名，设置@predepartment表示上一个元组的departmentid，如果@predepartment和当前departmentid相同@rank自加，否则@rank为1；

该过程SQL语句如下：

select salary,@rank:=if(@predepartment=departmentid,@rank+1,1) as rank,@predepartment:=departmentid as departmentid 
from 
(select distinct salary,departmentid from Employee order by departmentid asc,salary desc) e0,(select @rank:=0) r,(select @predepartment:=null) p

得到的结果如下所示：

+--------+------+--------------+
| salary | rank | departmentid |
+--------+------+--------------+
|  90000 |    1 |            1 |
|  85000 |    2 |            1 |
|  70000 |    3 |            1 |
|  69000 |    4 |            1 |
|  80000 |    1 |            2 |
|  60000 |    2 |            2 |
+--------+------+--------------+

然后找出表中rank=3的元组就可以了，这里可能会有人发现有些部门的记录不足三个的时候，直接取rank=3时就没有该部门的记录，对于这种情况我们就得union一下该部门salary最低的记录了。

需要union的表的查询语句为：

select min(salary) as salary,departmentid from (select salary,departmentid from Employee group by salary,departmentid) e1 group by departmentid having count(*)<3

通过以上步骤，中间结果表就产生了，本例的结果如下： +--------+--------------+
| salary | departmentid |
+--------+--------------+
|  70000 |            1 |
|  60000 |            2 |

+--------+--------------+

然后找出对应部门中，薪资大于该表salary的记录即可。

完整的sql语句如下：

select d.name,e.name,e.salary from 
Department d,
(select salary,departmentid from (select salary,@rank:=if(@predepartment=departmentid,@rank+1,1) as rank,@predepartment:=departmentid as departmentid from (select distinct salary,departmentid from Employee order by departmentid asc,salary desc) e0,(select @rank:=0) r,(select @predepartment:=null) p) s where rank=3 
union 
select min(salary) as salary,departmentid from (select salary,departmentid from Employee group by salary,departmentid) e1 group by departmentid having count(*)<3) l,
Employee e 
where e.departmentid = d.id and e.departmentid = l.departmentid and e.salary >= l.salary