【SQL】184. Department Highest Salary

最新推荐文章于 2025-09-05 14:24:44 发布

转载最新推荐文章于 2025-09-05 14:24:44 发布 · 103 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/fcyworld/p/6498812.html

文章标签：

#数据库

本文介绍了一种使用SQL查询的方法，来找出每个部门中薪资最高的员工。通过联接Employee和Department表，并使用子查询来确定每个部门的最大薪资，从而得到最终结果。

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  |
+------------+----------+--------+

# Write your MySQL query statement below
select d.Name Department, e.Name Employee, e.Salary
from Employee e,Department d
where e.DepartmentId=d.Id
and (e.DepartmentId,e.Salary) in
    (
    select DepartmentId,max(Salary)
    from Employee
    group by DepartmentId
    );