[mySQL] 리트코드 184. Department Highest Salary 2가지 풀이 방법

https://leetcode.com/problems/department-highest-salary/

Write an SQL query to find employees who have the highest salary in each of the departments.
Return the result table in any order.
The query result format is in the following example.

1. 각 부서별로 월급이 가장 높은 직원을 출력
2. 정렬 조건 없음
   

1. FROM 절 서브쿼리, JOIN 사용해서 풀기

SELECT d.name AS Department, e.name AS Employee, e.salary AS Salary
FROM Employee AS e INNER JOIN (
                   SELECT departmentId, MAX(salary) AS earnings
                   FROM Employee
                   GROUP BY departmentId) AS max_salary ON e.departmentId = max_salary.departmentId
                   AND e.salary = max_salary.earnings
                   INNER JOIN Department AS d ON e.departmentId = d.id

2. 윈도우 함수, FROM 절 서브쿼리, JOIN 사용해서 풀기

SELECT Department.name AS Department,
       max.name AS Employee,
       max.salary AS Salary
FROM 
    (SELECT id, name, salary, departmentId, MAX(salary) OVER (PARTITION BY departmentId) AS max_salary
     FROM Employee) AS max
     LEFT JOIN Department ON max.departmentId = Department.id
WHERE max.salary = max.max_salary

☑️ point

SELECT 절에서 연산을 해준 것은 WHERE 절에서 필터링 조건으로 사용할 수 없다
-> 서브쿼리로 묶어준 후 WHERE 절 필터링 조건으로 사용한다!

서브쿼리.. 어렵다...^^........ 연습을 많이 해야겠음 ㅜㅜ