184. Department Highest Salary#1-优快云博客

本文链接：https://blog.youkuaiyun.com/u014521967/article/details/70138952

本文介绍了一种SQL查询方法，用于找出每个部门中薪资最高的员工。通过使用内连接和子查询，可以有效地从Employee和Department两个表中筛选出所需信息。

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

题目摘要
选出每栋Department中薪水最高的人(每栋楼不知一个)

解法
Solution1

# Write your MySQL query statement below
SELECT d.Name AS Department, e.Name As Employee, e.Salary
From Employee AS e
INNER JOIN (
    SELECT max(Salary) AS Salary, DepartmentId
    FROM Employee
    GROUP BY DepartmentId
    ) AS et
ON e.Salary = et.Salary AND e.DepartmentId = et.DepartmentId
INNER JOIN Department AS d
ON e.DepartmentId = d.Id

注意
Solution1
1. 由于不止一个，所以连表的主表不能是GROUP BY出来的字表

可问问题

原题
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 |
+————+———-+——–+