[SQL] Department Highest Salary(LEETCODE)✅

📝 문제 설명

Column Name	Type
id	int
name	varchar
salary	int
departmentId	int

id is the primary key (column with unique values) for this table.
departmentId is a foreign key (reference columns) of the ID from the Department table.
Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.

Column Name	Type
id	int
name	varchar

id is the primary key (column with unique values) for this table. It is guaranteed that department name is not NULL.
Each row of this table indicates the ID of a department and its name.

❓ 문제

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.

📖 예시

Employee table:

id	name	salary	departmentId
1	Joe	70000	1
2	Jim	90000	1
3	Henry	80000	2
4	Sam	60000	2
5	Max	90000	1

Department table:

id	name
1	IT
2	Sales

Output:

Department	Employee	Salary
IT	Jim	90000
Sales	Henry	80000
IT	Max	90000

Explanation: Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

💻 코드

내코드

with max as(
select 
    e.departmentid
    ,max(e.salary) as max
    ,d.name
from employee e
left join department d
on e.departmentid = d.id
group by departmentid
)

select  
    m.name as Department
    ,e.name as Employee
    ,e.salary as Salary
from employee e
inner join max m 
on e.departmentid = m.departmentid
and m.max = e.salary

🔍 분석

-WITH 구문으로 각 부서별 가장 연봉이 얼마인지 잡고, 직원 테이블에서 겹치는 연봉이 있어도 상관 없으니 가장 큰 연봉과 맞는 인원들 모두 추출