LeetCode—部门工资前三高的员工

LeetCode中提供的Employee表中有两个记录的缺失，在这里补充上。
SQL架构：

Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int);
Create table If Not Exists Department (Id int, Name varchar(255));
Truncate table Employee;
insert into Employee (Id, Name, Salary, DepartmentId) values ('1', 'Joe', '70000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('2', 'Henry', '80000', '2');
insert into Employee (Id, Name, Salary, DepartmentId) values ('3', 'Sam', '60000', '2');
insert into Employee (Id, Name, Salary, DepartmentId) values ('4', 'Max', '90000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('5', 'Janet', '69000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('6', 'Randy', '85000', '1');
Truncate table Department;
insert into Department (Id, Name) values ('1', 'IT');
insert into Department (Id, Name) values ('2', 'Sales');

查看所有记录：

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)

mysql> select * from department;
+------+-------+
| Id   | Name  |
+------+-------+
|    1 | IT    |
|    2 | Sales |
+------+-------+
2 rows in set (0.00 sec)

要求：
编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

解法:

mysql> select d.name as Department,e1.name as Employee,e1.salary
    -> from employee e1 left join department d on e1.departmentid=d.id
    -> where
    -> (select count(distinct e2.salary) from employee e2
    -> where e2.salary>e1.salary and e2.departmentid=e1.departmentid)<3
    -> order by d.name,e1.salary desc;
+------------+----------+--------+
| Department | Employee | salary |
+------------+----------+--------+
| IT         | Max      |  90000 |
| IT         | Randy    |  85000 |
| IT         | Joe      |  70000 |
| Sales      | Henry    |  80000 |
| Sales      | Sam      |  60000 |
+------------+----------+--------+
5 rows in set (0.00 sec)