Mysql 查询工资前三高的员工

题目

表: Employee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
Id是该表的主键列。
departmentId是Department表中ID的外键。
该表的每一行都表示员工的ID、姓名和工资。它还包含了他们部门的ID。
 

表: Department

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
Id是该表的主键列。
该表的每一行表示部门ID和部门名。
 

公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。

编写一个SQL查询，找出每个部门中 收入高的员工 。

以 任意顺序 返回结果表。

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/department-top-three-salaries
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

题解

WITH ts AS (SELECT departmentId,salary FROM Employee

        GROUP BY departmentId,salary ORDER BY departmentId,salary DESC),
tm AS (SELECT departmentId,salary,rank() over(PARTITION BY departmentId ORDER BY salary DESC) as seq FROM ts)
SELECT Department.name AS Department, Employee.name AS Employee, Employee.salary AS Salary
FROM Employee,Department,tm
WHERE tm.seq <=3 AND Employee.departmentId=Department.id AND tm.departmentId= Employee.departmentId AND tm.salary=Employee.salary;