[SQL] LeetCode : 184. Department Highest Salary

 

184. Department Highest Salary

LeetCode - The World's Leading Online Programming Learning Platform

 

 

-- 1. 출력: department name, employee name, salary
-- 2. 필터: the highest salary in each of the departments
-- 3. 정렬: X

WITH max AS(
    SELECT e.name AS Employee
          ,e.salary AS salary
          ,d.name AS Department
          ,MAX(e.salary) OVER (PARTITION BY d.name) AS dh_salary
    FROM employee e
        LEFT JOIN department d ON e.departmentId = d.id
)

SELECT Department
      ,Employee 
      ,Salary
FROM max 
WHERE Salary = dh_salary

 

 

WITH문과 윈도우 함수를 사용하여 풀이

employee 테이블과 department 테이블에 이름이 같은 컬럼이 있어서 필요한 변수만 WITH문에서 출력

여러가지 방법으로 풀어봤는데 가장 간단한 것 같은 풀이로 포스팅 해본다 :)