[LeetCode] department-highest-salary 풀이

[문제]

department-highest-salary 문제 바로 가기

Department Highest Salary - LeetCode

 

[풀이]

 

 

SELECT de.name Department
     , em.name Employee
     , em.salary Salary
FROM employee em
    JOIN (
      SELECT departmentID, MAX(salary) max
      FROM employee
      GROUP BY departmentID) hi ON hi.departmentID = em.departmentID 
                                AND hi.max = em.salary 
    JOIN department de ON em.departmentID = de.id

 

- 윈도우 함수 MAX() 이용 풀이 

 

SELECT d.name AS Department
     , s.name AS Employee
     , maxsalary AS Salary
FROM(
    SELECT name
     , salary
     , departmentID
     , MAX(salary) OVER (PARTITION BY departmentID) AS maxsalary
    FROM employee) s
INNER JOIN department d ON d.id = s.departmentID
WHERE s.salary = s.maxsalary;