题目内容如下(链接: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