d202552-sql

一、184. 部门工资最高的员工 - 力扣（LeetCode）

要找到每个部门工资最高的

使用窗口函数 加排序函数

排序函数用rank dense_rank都行  把最高相同的找出来就行

 

select *,
dense_rank() over(partition by departmentId  order by Salary desc) as 'rank' 
from Employee

 有没有朋友  也想过为啥这里不能直接用where

select *,
dense_rank() over(partition by departmentId  order by Salary desc) as 'rank' 
from Employee
where rank = 1

1. ​​SQL 执行顺序​​

SQL 查询的逻辑执行顺序为：

1. FROM → 2. WHERE → 3. GROUP BY → 4. HAVING → 5. SELECT → 6. ORDER BY
   ​​关键点​​：WHERE 子句在 SELECT 之前执行，而 SELECT 中定义的别名（如 rank）此时尚未生成，因此无法被识别

把上述那个表当成临时表，来进行两表连接 

select d.name as 'Department',e.name as 'Employee',e.salary as 'Salary'
from Department d
join (select *,dense_rank() over(partition by departmentId  order by Salary desc) as 'rank' from Employee) e
on d.id = e.departmentId 
where e.rank = 1

 搞定 说实话窗口函数真好用！！！！！！

二、185. 部门工资前三高的所有员工 - 力扣（LeetCode）

同理上题

但是这里的排序函数只能 DENSE_RANK()​，相同值分配相同排名，后续排名连续

where 里面的条件在加2个

select d.name as 'Department',e.name as 'Employee',e.salary as 'Salary'
from Department d
join (select *,dense_rank() over(partition by departmentId  order by Salary desc) as 'rank' from Employee) e
on d.id = e.departmentId 
where e.rank = 1 or e.rank = 2 or e.rank = 3

 

三、577. 员工奖金 - 力扣（LeetCode）

需要使用左连接或者右连接完全保留员工表的数据

select e.name,b.bonus
from Employee e left join Bonus b
on e.empId = b.empId 
where bonus < 1000 or bonus is null