Employee 表包含所有员工信息,每个员工有其对应的工号 Id,姓名 Name,工资 Salary 和部门编号 DepartmentId 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询,找出每个部门获得前三高工资的所有员工。例如,根据上述给定的表,查询结果应返回:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
一、表的连接
先连接两个Employee表找出每个部门前三的员工,再连接Department表显示结果。
select d.name as Department, e1.name as Empolyee, e1.salary as Salary
from Employee as e1
join Department as d
on e1.DepartmentId = d.Id
where (select count(distinct e2.name) from Employee as e2 where e1.salary<e2.salary and e1.DepartmentId = e2.DepartmentId)<3
二、dense_rank()函数
先组内排序,选出每组前三高的薪水,再连接Department表得出结果。
select d.name as Department, a.name as Empolyee, a.salary as Salary
from (select e.name,e.salary,e.DepartmentId,dense_rank()over(partition by DepartmentId order by salary desc) as 'newId'
from Employee as e) as a
join Department as d
on a.DepartmentId = d.Id
where a.newId<=3
三、自定义变量
先给每个部门内部的薪水高低排序的得出rank,再连接Department表得出部门名称,再连接Employee表找出排名前三的薪水和部门对应的员工。
select d.name as Department, e2.name as Employee, e2.salary as Salary
from (
select ee.DepartmentId,ee.salary,
case when @p = DepartmentId then @r := @r+1
when @p:= DepartmentId then @r := 1
end as 'rank'
from (select @p:=null,@r:= 0)temp,
(select DepartmentId, Salary from Employee group by DepartmentId,Salary order by DepartmentId, Salary desc)as ee
) as e1
join Department as d
on e1.DepartmentId = d.Id
join Employee as e2
on e1.DepartmentId = e2.DepartmentId and e1.rank<=3 and e1.salary = e2.salary