[문제풀이] Leet code - Department Highest Salary

출처

 

문제

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

문제에 대한 해석

 

각 부서별 가장높은 급여를 받는 사람(공동순위 가능)의 부서, 이름, 연봉을 조회

 

풀이(MYSQL)

-- 1.서브쿼리 + dense_rank
select d.name as 'Department', a.name as 'Employee', a.salary as 'Salary'
from
(select dense_rank()over(partition by departmentID order by salary desc) as 'rn',
    id, name, departmentID,salary
from Employee) a join Department d
on a.departmentID = d.id
where rn = 1

-- 2. with 구문 + dense_rank
with base as (
    select dense_rank()over(partition by departmentID order by salary desc) as 'rn',
    id, name, departmentID,salary
    from Employee)
select d.name as 'Department', a.name as 'Employee', a.salary as 'Salary'
from base a join Department d
on a.departmentID = d.id
where rn = 1

-- 3. where, in
SELECT Department.name AS Department ,Employee.name AS Employee, Employee.salary
FROM Department  JOIN Employee  ON Employee.departmentId=Department.id 
WHERE(departmentId, salary) IN
(SELECT departmentId,MAX(salary) FROM Employee GROUP BY departmentId) ;

 

작동순서

💡SQL 실행 순서는 From -> Where -> Group by -> Having -> Select -> Order by ->결과반환

1. FROM 절: Department 테이블과 Employee테이블을 부서 id를 기준으로 조인하여
2. WHERE 절: Employee을 departmentId별로 그룹화하여 departmentId별 가장 높은 salary에 포함되는 departmentId와 salary를 필터링 하여
3. SELECT 절: Department의 name컬럼을 Department로, Employee의 name컬럼을 Employee로 별칭하여 salary와 함께 조회히라

배운점

• 런다임 시간 : 3 > 2 > 1
• 윈도우함수를 쓰는게 항상 좋은 거는 아닌것 같다.