0%

部门工资前三高的员工

题目描述

Employee 表包含所有员工信息,每个员工有其对应的工号 Id,姓名 Name,工资 Salary 和部门编号 DepartmentIdDepartment 表包含公司所有部门的信息。编写一个 SQL 查询,找出每个部门获得前三高工资的所有员工。

题解

  1. 如何找出每个部门排名前三的工资值?两张Employee表做笛卡尔积,筛选留下同一部门的、工资不低于自己的记录,并且比自己工资高的数值不能小于3个。
1
2
select * from Employee as e1
where 3 > (select count(distinct e2.Salary) from Employee as e2 where e1.Salary < e2.Salary and e1.DepartmentId = e2.DepartmentId)
  1. 将 Employee 和 Department 合并
1
2
select d1.Name as Department, e1.Name as Employee, e1.Salary as Salary from Employee as e1, Department as d1
where e1.DepartmentId = d1.Id and 3 > (select count(distinct e2.Salary) from Employee as e2 where e1.Salary < e2.Salary and e1.DepartmentId = e2.DepartmentId)