184. Department Highest Salary

本文介绍了一个SQL查询案例,旨在找出每个部门中薪资最高的员工。通过使用子查询和连接操作,可以有效地从Employee和Department两个表中获取所需信息。

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

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

The Department table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

Subscribe to see which companies asked this question.


MySQL:

# Write your MySQL query statement below

select 
    dd.Department   as Department,
    ee.Name         as Employee,
    dd.Salary       as Salary
from
    Employee ee,
    (
    select 
        max(e.DepartmentId)     as DepartmentId,
        d.Name                  as Department,
        max(e.Salary)           as Salary
    from
        Employee e,
        Department d
    where
        e.DepartmentId = d.Id
    group by
        d.Name
    ) dd
where
    ee.Salary = dd.Salary
    and ee.DepartmentId = dd.DepartmentId
;


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值