mysql 自连接 树形,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

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

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