Employee 表包含所有員工信息,每個員工有其對應的 Id, salary 和 department Id。

編寫一個 SQL 查詢,找出每個部門工資最高的員工。例如,根據上述給定的表格,Max 在 IT 部門有最高工資,Henry 在 Sales 部門有最高工資。

IdNameSalaryDepartmentId
1Joe700001
2Henry800002
3Sam600002
4Max900001

Department 表包含公司所有部門的信息。

IdName
1IT
2Sales
DepartmentEmployeeSalary
ITMax90000
SalesHenry80000

解答

  1. 子查詢獲取每個部門最高的工資。
  2. 再使用 WHERE IN 篩選。
 1SELECT
 2    d.Name as Department,
 3    e.Name as Employee,
 4    e.Salary as Salary
 5FROM
 6    Employee e
 7LEFT JOIN
 8    Department d
 9        ON e.DepartmentId = d.Id
10WHERE (e.DepartmentId, e.Salary) IN (
11    SELECT
12        DepartmentId,
13        MAX(Salary)
14    FROM
15        Employee
16    GROUP BY
17        DepartmentId
18) AND d.Id IS NOT NULL
19ORDER BY e.Id

ORDER BY e.Id 是無關緊要的,只是結果集順序問題。

原題

來源:力扣(LeetCode)
鏈接:https://leetcode-cn.com/problems/department-highest-salary