[LeetCode] Department Highest Salary

with t1 as (
select d.name as Department,
       e.name as Employee,
       e.salary
from employee as e
 inner join department as d on d.id = e.departmentid)
 
 
select t1.department,
       t1.employee,
       t1.salary
from t1
 left join (select max(salary) as top_salary,
                   Department
            from t1
            group by department) as t2
            on t2.department = t1.department
where t1.salary = t2.top_salary

with문으로 join한 테이블을 만들어 놓고, 이것을 사용하여 쿼리 작성.

department별로 max(salary)를 구하고 이것을 join하여 where절에서 값 비교로 조건을 넣을 수 있게 만듦.

 

(문제 출처 - https://leetcode.com/problemset/database/)