[LeetCode] Department Highest Salary -- 資料庫知識(mysql)

標籤：tables   rtm   write   find   any   weight   nbsp   employee   mysql   

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

解答一：

SELECT D.Name AS Department ,E.Name AS Employee ,E.Salary from     Employee E,    Department D WHERE E.DepartmentId = D.id   AND (E.DepartmentId,E.Salary) in   (SELECT DepartmentId,max(Salary) as max FROM Employee GROUP BY DepartmentId)

解題思路是利用WHERE串連子句來構造篩選條件，子句匯中構造了一個新的表max，用到了IN操作符來選擇DepartmentId 和Salary，group by 用來根據DepartmentId分組以便分別

求每個系的最高薪水。

解答二：

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

和一稍有不同，直接從三個表中選取，將新構造的表放到了FROM子句裡邊，其他直接以別名取代，以and串連三個條件。

 

[LeetCode] Department Highest Salary -- 資料庫知識(mysql)