Leetcode_184_部门工资最高的员工_SQL

最新推荐文章于 2025-05-03 01:00:00 发布

原创最新推荐文章于 2025-05-03 01:00:00 发布 · 123 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#leetcode #sql #算法

daily_algorithm 同时被 2 个专栏收录

230 篇文章

订阅专栏

SQL

27 篇文章

订阅专栏

该博客探讨了一种SQL查询方法，用于从员工和部门表中找出每个部门薪水最高的员工及其详细信息。通过子查询和JOIN操作，实现了对数据的筛选和聚合，确保每个部门只显示一个最高薪资的员工记录。

select d.name as 'department',e.name as 'employee', salary
from employee as e join department as d
where e.departmentId = d.id and 
(e.departmentId, e.salary) in (select e.departmentId, max(e.salary) as s
from employee as e, department as d
group by e.departmentId)

with t as (select departmentId as did, max(salary) as s
from employee
group by departmentId)

select d.name as 'department',e.name as 'employee', t.s as 'salary'
from t
left join employee as e on t.s = e.salary and e.departmentId = t.did
left join department as d on t.did = d.id