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

letcode——185. 部门工资前三高的所有员工

1、使用表连接(join)进行求解:
解题思想:先求出部门前三工资,然后利用里表连接获取对应的信息。

首先求出部门前三工资:

select e1.DepartmentId, e1.Salary from Employee e1
        left join Employee e2 
        on e1.DepartmentId = e2.DepartmentId and e1.Salary < e2.Salary
        group by e1.DepartmentId, e1.Salary
        having count(distinct(e2.Salary)) < 3 )

解释:
使用Employee原表建立表e1和e2
e1 left join e2
每当存储引擎拿到一条数据时,就会完全遍历e2的数据(join的工作原理!)
只要count(distinct(e2.Salary)) < 3,就能说明该数据时前3名

然后就是对表之间的连接,这里没什么好讲的。
以下是完整sql代码:

select t3.Name Department, t1.Name Employee, t1.Salary
from Employee t1
join (
    select e1.DepartmentId, e1.Salary from Employee e1
        left join Employee e2 
        on e1.DepartmentId = e2.DepartmentId and e1.Salary < e2.Salary
        group by e1.DepartmentId, e1.Salary
        having count(distinct(e2.Salary)) < 3 )
        t2
on t1.DepartmentId = t2.DepartmentId and t1.Salary = t2.Salary
join Department t3 on t1.DepartmentId = t3.Id

2、使用where进行求解

SELECT
	Department.NAME AS Department,
	e1.NAME AS Employee,
	e1.Salary AS Salary 
FROM
	Employee AS e1,Department 
WHERE
	e1.DepartmentId = Department.Id 
	AND (SELECT  count( DISTINCT e2.Salary ) 
			 FROM	Employee AS e2 
			 WHERE	e1.DepartmentId = e2.DepartmentId
             AND  e1.Salary < e2.Salary	) < 3
ORDER BY Department.NAME,Salary DESC;

3 、使用窗口函数进行求解

select d.name AS Department,a.name AS Employee,a.Salary
from 
(
select *,dense_rank() over(partition by departmentid order by salary desc) as t from Employee
) as a,department as d
WHERE a.departmentid = d.id and t<=3

总结

1、对于join的深刻理解:一条条取出驱动表符合条件的数据,被驱动表进行全局扫描。
2、对于表连接的两种方式,where和join的相互转换
3、窗口函数rank, dense_rank, row_number

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值