心得

題目要求找出每個部門薪水最高的員工，若同部門最高薪水有複數則一起顯示，這題我在JOIN部門清單的時候先去把各部門最高薪資數字先撈了出來，這樣在關聯兩張表的時候就可以直接搜尋員工薪水是否等於最高薪資。

問題

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

答案

1. JOIN部門清單的時候先去把各部門最高薪資數字先撈了出來

   # Write your MySQL query statement below
   SELECT  b.Name AS Department,
           a.Name AS Employee,
           a.Salary AS Salary
   FROM Employee AS a
   JOIN (  SELECT z.*, (SELECT MAX(y.Salary)
                        FROM Employee AS y
                        WHERE z.Id = y.DepartmentId
                        LIMIT 0, 1) AS MaxSalary
           FROM Department AS z) AS b
   ON a.DepartmentId = b.Id AND a.Salary = b.MaxSalary

2. (2017/01/16) 寫 185. Department Top Three Salaries 時後發現的方法

   # Write your MySQL query statement below
   SELECT  b.Name AS Department,
           a.Name AS Employee,
           a.Salary AS Salary
   FROM Employee AS a
   JOIN Department AS b
   ON a.DepartmentId = b.Id
   WHERE ( SELECT COUNT(DISTINCT(z.Salary)) 
           FROM Employee AS z
           WHERE a.DepartmentId = z.DepartmentId
           AND a.Salary < z.Salary) = 0