[LeetCode]184. Department Highest Salary

問題描述

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	Jim	90000	1
3	Henry	80000	2
4	Sam	60000	2
5	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, your SQL query should return the following rows (order of rows does not matter).

Department	Employee	Salary
IT	Max	90000
IT	Jim	90000
Sales	Henry	80000

Explanation:

Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

翻譯

Employee 這張資料表擁有所有員工資訊，每一筆資料包含員工編號、名稱、薪水、以及部門代碼
Department 這張資料表擁有公司部門資訊
請撰寫一段 SQL 查詢能找出各個部門薪資最高的員工，並參考以下查詢結果格式作為解答標準（次序非必要條件）。

解題思維

1. 部門代號可以利用 JOIN 方式將 Employee 與 Department 兩張作關聯，如此以來就可以取得部門名稱
2. 要取得部門中最高薪資，使用 Group By 部門方式，則可以取到該部門最高薪資
3. 最後  將 1.查詢結果 JOIN2.，以薪水、部門代號作為關聯條件，就可以得出答案

解題報告

Level: Medium
Runtime: 983 ms, faster than 89.78% of Oracle online submissions for Department Highest Salary.
Memory Usage: 0B, less than 100.00% of Oracle online submissions for Department Highest Salary.

程式完整解題

/* Write your PL/SQL query statement below */

SELECT DP.NAME   AS DEPARTMENT -- 部門名稱
      ,EM.NAME   AS EMPLOYEE   -- 員工名稱
      ,EM.SALARY AS SALARY     -- 員工薪水
  FROM EMPLOYEE EM
 INNER JOIN DEPARTMENT DP
         ON EM.DEPARTMENTID = DP.ID
 INNER JOIN (SELECT    DEPARTMENTID
                      ,MAX(SALARY)   AS SALARY
               FROM    EMPLOYEE
              GROUP BY DEPARTMENTID) MAX_SALARY
         ON EM.SALARY = MAX_SALARY.SALARY
        AND EM.DEPARTMENTID = MAX_SALARY.DEPARTMENTID;

SQL Schema

Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int)
Create table If Not Exists Department (Id int, Name varchar(255))
Truncate table Employee
insert into Employee (Id, Name, Salary, DepartmentId) values ('1', 'Joe', '70000', '1')
insert into Employee (Id, Name, Salary, DepartmentId) values ('2', 'Jim', '90000', '1')
insert into Employee (Id, Name, Salary, DepartmentId) values ('3', 'Henry', '80000', '2')
insert into Employee (Id, Name, Salary, DepartmentId) values ('4', 'Sam', '60000', '2')
insert into Employee (Id, Name, Salary, DepartmentId) values ('5', 'Max', '90000', '1')
Truncate table Department
insert into Department (Id, Name) values ('1', 'IT')
insert into Department (Id, Name) values ('2', 'Sales')