mysql 自连接

现有一Employee 表，详情如下

mysql> select * from Employee;
+----+-------+--------+--------------+
| 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 |
+----+-------+--------+--------------+
6 rows in set (0.00 sec)

求每一个部门DepartmentId中Salary最高的前三名

mysql> select * from Employee e1 where (select count(distinct Salary) from Employee e2 where e1.DepartmentId=e2.DepartmentId and e1.Salary<e2.Salary) < 3 order by e1.DepartmentId,e1.Salary desc;
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
|  4 | Max   |  90000 |            1 |
|  6 | Randy |  85000 |            1 |
|  1 | Joe   |  70000 |            1 |
|  2 | Henry |  80000 |            2 |
|  3 | Sam   |  60000 |            2 |
+----+-------+--------+--------------+
5 rows in set (0.00 sec)

或者

mysql> select * from Employee E where (select count(distinct Salary) from Employee where DepartmentId=E.DepartmentId and Salary>E.Salary)<3 order by DepartmentId,Salary desc;
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
|  4 | Max   |  90000 |            1 |
|  6 | Randy |  85000 |            1 |
|  1 | Joe   |  70000 |            1 |
|  2 | Henry |  80000 |            2 |
|  3 | Sam   |  60000 |            2 |
+----+-------+--------+--------------+
5 rows in set (0.00 sec)