LeetCode Database 185 部门工资前三高的员工

1. 题目描述

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 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            |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

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

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

2. 题解

2.1. MySQL（ORDER + LIMIT）

SELECT tb2.Name AS Department, tb1.Name AS Employee, tb1.Salary
FROM Employee tb1
JOIN Department tb2 ON tb1.DepartmentId = Id
WHERE tb1.Salary IN (
    SELECT tb3.DepartmentId, tb3.Salary FROM Employee tb3
    WHERE tb1.DepartmentId = tb3.DepartmentId
    ORDER BY tb3.Salary DESC LIMIT 3
)

出错信息：IN/ALL/ANY/SOME子查询不支持LIMIT

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

2.2. MySQL（COUNT统计TON N）

SELECT tb2.Name AS Department, tb1.Name AS Employee, tb1.Salary
FROM Employee tb1
JOIN Department tb2 ON tb1.DepartmentId = tb2.Id
WHERE (
    -- COUNT统计该部门下，比当前员工Salary大的不同的Salary有多少个，如果小于3，说明当前员工的Salary属于前3
    SELECT COUNT(DISTINCT Salary)
    FROM Employee tb3
    WHERE tb1.DepartmentId = tb3.DepartmentId
    AND tb1.Salary < tb3.Salary) < 3