这是我参与2022首次更文挑战的第11天，活动详情查看：2022首次更文挑战

题目

部门工资前三高的所有员工

解析

这也算是个经典的较难SQL了。

回想一下，如果是从编程语言的角度来解决这个问题，我们会怎么做？

• 按照部门，取出每个部门前三高的工资。
• 当然，可以和这个工资一起关联，带上员工最好了；不行的话，取出前三高工资，再拿这个工资去取员工数据即可。

那么，是否可以按照这个思路来写SQL？

首先第一步：如何取出每个部门前三高的工资？

单个部门当然简单了(假设部门id是1)：

select distinct salary from Employee where DepartmentId = 1 order by salary desc limit 0,3

但是，如果要在一条sql里把每个部门前三高的拉出来，那就麻烦了，因为order by 的话就没部门属性了。

那么根据这个limit 0,3 ，换个思路：前三高就代表着比结果集中的数大的工资数小于等于2,那么查找出每个部门中满足这个条件的所有工资，就是所要的每个部门前三高的工资。

如果暂时想不到如何解决，不妨可以先把表中同部门的工资和不大于这个工资的记录做个笛卡尔积：

select e1.salary as es1,e1.DepartmentId as DepartmentId,e2.salary as es2
from 
(
select distinct salary,DepartmentId from Employee
) e1 left JOIN
(
select distinct salary,DepartmentId from Employee
) e2 on e1.DepartmentId = e2.DepartmentId and e1.salary<=e2.salary

这样子就可以得出某个工资和同部门的其他工资的对照记录。

由于我们的笛卡尔积包括工资自身，因此条件就是count(es2)<=3:

select es1,DepartmentId from 
(
select e1.salary as es1,e1.DepartmentId as DepartmentId,e2.salary as es2
from 
(
select distinct salary,DepartmentId from Employee
) e1 left JOIN
(
select distinct salary,DepartmentId from Employee
) e2 on e1.DepartmentId = e2.DepartmentId and e1.salary<=e2.salary
) a 
group by es1,DepartmentId
having count(es1<es2)<=3

拿着这个去找对应的关系即可得到结果：

select d.name as Department,e.name as Employee,e.salary as Salary
from 
(
select es1,DepartmentId from 
(
select e1.salary as es1,e1.DepartmentId as DepartmentId,e2.salary as es2
from 
(
select distinct salary,DepartmentId from Employee
) e1 left JOIN
(
select distinct salary,DepartmentId from Employee
) e2 on e1.DepartmentId = e2.DepartmentId and e1.salary<=e2.salary
) a 
group by es1,DepartmentId
having count(es1<es2)<=3
) datum left join Employee e on e.salary = datum.es1 and e.DepartmentId = datum.DepartmentId
left join Department d 
on e.DepartmentId = d.id
order by Department,Salary desc

\