心得
題目要求找出每個部門薪水最高的員工,若同部門最高薪水有複數則一起顯示,這題我在JOIN
部門清單的時候先去把各部門最高薪資數字先撈了出來,這樣在關聯兩張表的時候就可以直接搜尋員工薪水是否等於最高薪資。
問題
The
Employee
table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.
12345678 +----+-------+--------+--------------+| 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.
123456 +----+----------+| 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.
123456 +------------+----------+--------+| Department | Employee | Salary |+------------+----------+--------+| IT | Max | 90000 || Sales | Henry | 80000 |+------------+----------+--------+
答案
JOIN
部門清單的時候先去把各部門最高薪資數字先撈了出來
1234567891011# Write your MySQL query statement belowSELECT b.Name AS Department,a.Name AS Employee,a.Salary AS SalaryFROM Employee AS aJOIN ( SELECT z.*, (SELECT MAX(y.Salary)FROM Employee AS yWHERE z.Id = y.DepartmentIdLIMIT 0, 1) AS MaxSalaryFROM Department AS z) AS bON a.DepartmentId = b.Id AND a.Salary = b.MaxSalary- (2017/01/16) 寫 185. Department Top Three Salaries 時後發現的方法
1234567891011# Write your MySQL query statement belowSELECT b.Name AS Department,a.Name AS Employee,a.Salary AS SalaryFROM Employee AS aJOIN Department AS bON a.DepartmentId = b.IdWHERE ( SELECT COUNT(DISTINCT(z.Salary))FROM Employee AS zWHERE a.DepartmentId = z.DepartmentIdAND a.Salary < z.Salary) = 0