Leetcode之mysql专题《Department Top Three Salaries》

题目内容如下（链接：https://leetcode.com/problems/department-top-three-salaries/description/）：

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

题解思路如下：

1. 先设计出答案的主体结构如下（返回Department、Employee和Salary3个字段，这3个字段的获取需要关联表e1和表d）

SELECT
    d.Name as Department, e1.Name as Employee, e1.Salary as Salary
FROM
    Employee e1
JOIN
    Department d ON d.Id = e1.DepartmentId

2. 接下来设计where语句，只需要返回每个部门工资在top3的员工信息，所以需要再遍历一次整个e2表查询同个部门工资比当前员工高的个数，只有个数小于3的员工信息才符合要求，于是有

WHERE
    (
    SELECT 
        COUNT(DISTINCT(e2.Salary))
    FROM
        Employee e2
    WHERE
        e2.DepartmentId = e1.DepartmentId AND e2.Salary > e1.Salary
    ) < 3

3. 最后把每个分组的结果按照部门编号升序，工资降序的顺序排序（测试发现这部可有可无）

ORDER BY e1.DepartmentId ASC, e1.Salary DESC

4.  汇总一下即可得到问题的答案如下

SELECT
    d.Name as Department, e1.Name as Employee, e1.Salary as Salary
FROM
    Employee e1
JOIN
    Department d ON d.Id = e1.DepartmentId
WHERE
    (
    SELECT 
        COUNT(DISTINCT(e2.Salary))
    FROM
        Employee e2
    WHERE
        e2.DepartmentId = e1.DepartmentId AND e2.Salary > e1.Salary
    ) < 3
ORDER BY e1.DepartmentId ASC, e1.Salary DESC