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

这个题目属于hard级别，难点在于先组内排序，然后取组内前三的数据出来（排名可并列），最后再做一个组间排序。

因此，我的解题思路是这样的：

1.把数据按照DepartmentId，Salary 排序，这样的话同一部门的数据在搜索的结果集中就在一起。

select DepartmentId, Name  ,Salary from Employee  order by DepartmentId ,Salary  desc

2.对同一部门的数据的记录进行编号，从1开始，如果和上一行的薪水相同，则编号和上一行一样。

select DepartmentId, Salary,Name,        @num := if(@cid = DepartmentId ,if(@cursalry != Salary, @num + 1,@num), 1) as number,        @maxprice := if(@num = 1 ,@maxprice := Salary,@maxprice) as mp,        @cid := DepartmentId as dummy,        @cursalry:= Salary as curs      from  (select DepartmentId, Name  ,Salary from Employee  order by DepartmentId ,Salary  desc) e ,(select @num := 0,@maxprice := 0,@cid := 0,@cursalry = 0) b   ) c

3.过滤掉编号大于 3的记录。因为数据已经排序过，所以编号小于等于3的记录就是薪水的前三名。

select c.DepartmentId, c.Name ,c.Salary from      (      select DepartmentId, Salary,Name,            @num := if(@cid = DepartmentId ,if(@cursalry != Salary, @num + 1,@num), 1) as number,            @maxprice := if(@num = 1 ,@maxprice := Salary,@maxprice) as mp,            @cid := DepartmentId as dummy,            @cursalry:= Salary as curs          from  (select DepartmentId, Name  ,Salary from Employee  order by DepartmentId ,Salary  desc) e ,(select @num := 0,@maxprice := 0,@cid := 0,@cursalry = 0) b       ) c where c.number <=3      ) g

4.关联Department表，获取Department Name。

select f.Name as Department ,g.Name as Employee  ,g.Salary from      (      select c.DepartmentId, c.Name ,c.Salary from      (      select DepartmentId, Salary,Name,            @num := if(@cid = DepartmentId ,if(@cursalry != Salary, @num + 1,@num), 1) as number,            @maxprice := if(@num = 1 ,@maxprice := Salary,@maxprice) as mp,            @cid := DepartmentId as dummy,            @cursalry:= Salary as curs          from  (select DepartmentId, Name  ,Salary from Employee  order by DepartmentId ,Salary  desc) e ,(select @num := 0,@maxprice := 0,@cid := 0,@cursalry = 0) b       ) c where c.number <=3      ) g ,Department  f where  g.DepartmentId = f.Id ;