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

題目大意

編寫一個SQL查詢來查詢每個部門中薪水最高的員工。對於上面的表格，Max在IT部門工資最高，Henry在銷售部門工資最高。

解題思路

難得碰到一道簡單一點的,簡單記錄一下思路

首先將 Employee 與 Department 通過 DepartmentId 相連,滿足結果中的所需要的部門欄位

然後就是要找出每個部門中工資最高的人的集合,也就是在臨時表中查詢輸出每一部門最大的工資及部門號即可. 最後連結上述條件得

SELECT t.Department, p.Name AS Employee, t.Salary 
FROM Employee AS p,  
(SELECT d.Id,d.Name AS Department,MAX(e.Salary) AS Salary FROM Department AS d   
INNER JOIN Employee AS e ON d.Id = e.DepartmentId GROUP BY e.DepartmentId) AS t   
WHERE p.Salary = t.Salary AND p.DepartmentId = t.Id;  

看了一下結果details

這是目前最快的sql

select a.name Department, b.name Employee, Salary from employee b join department a on b.departmentid=a.id
where (departmentid, salary) in( (select departmentid, max(salary) from employee group by departmentid) )