LeetCode 0184 - Department Highest Salary

Reck Zhang

发布于 2021-08-11 14:55:29

3320

发布于 2021-08-11 14:55:29

Department Highest Salary

Desicription

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

Solution

SELECT 
    D.Name AS Department,
    E.Name AS Employee,
    E.Salary AS Salary
FROM
    Department D,
    Employee E,
    (SELECT DepartmentId AS Id, max(Salary) AS Salary FROM Employee GROUP BY DepartmentId) T
WHERE
    E.DepartmentId = D.Id AND
    E.DepartmentId = T.Id AND
    E.Salary = T.Salary

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2018-06-04，如有侵权请联系 cloudcommunity@tencent.com 删除

sql