部门工资前三高的员工

题目描述

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

题解

1. 如何找出每个部门排名前三的工资值？两张Employee表做笛卡尔积，筛选留下同一部门的、工资不低于自己的记录，并且比自己工资高的数值不能小于3个。

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)

2. 将 Employee 和 Department 合并

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)