leetcode_Oracle_184. 部门工资最高的员工

原创于 2020-04-09 00:44:00 发布 · 365 阅读

0 ·

CC 4.0 BY-SA版权

Oracle 同时被 2 个专栏收录

30 篇文章

订阅专栏

leetcode

9 篇文章

订阅专栏

本文介绍了三种使用SQL查询每个部门薪资最高的员工的方法。通过子查询、联接和窗口函数，展示了如何从员工和部门表中筛选出所需信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

184. 部门工资最高的员工

在这里插入图片描述

select depart.Name as "Department",
temp.Name as "Employee" ,
temp.Salary  as "Salary"
from 
Employee temp,Department  depart  
where temp.DepartmentId = depart.Id 
and
(temp.Salary,temp.DepartmentId) in 
(select max(Salary),DepartmentId from Employee group by DepartmentId); 




select depart.Name as "Department",
temp.Name as "Employee" ,
temp.Salary  as "Salary"
from 
Employee temp,Department  depart  
where temp.DepartmentId = depart.Id 
and temp.Salary >= (select max(Salary) from Employee where DepartmentId =depart.Id);




SELECT
SSS.Department as "Department",
SSS.Employee as "Employee" ,
SSS.Salary  as "Salary"
  FROM (
      SELECT 
depart.Name  as Department,
temp.Name as Employee ,
temp.Salary  as Salary,
dense_rank() OVER(PARTITION BY temp.DEPARTMENTID ORDER BY temp.SALARY DESC) RN
          FROM EMPLOYEE temp
          LEFT JOIN DEPARTMENT depart
            ON temp.DEPARTMENTID = depart.ID           
            ) SSS
 WHERE SSS.RN = 1;