[Leetcode] Department highest salary / N가지 풀이 (Subquery, Window Function)

https://leetcode.com/problems/department-highest-salary/

0. FROM절 서브쿼리

-- 1. 각 department별로 어떤 salary가 highest인지
SELECT departmentId,
    MAX(salary)
FROM employee
GROUP BY departmentId

 

-- 2. 전체 employee중 해당 department의 가장 salary를 많이 받는 사람만 뽑아낼 것
-- 1번 쿼리 결과를 Employee table에 INNER JOIN 해줌

-- 3. 원하는 형태로 출력 위해 Department를 JOIN
SELECT d.name AS Department,
    e.name AS Employee,
    e.salary
FROM Employee e
    INNER JOIN ( -- 2개 다 공통일때만 INNER JOIN
        SELECT departmentId,
            MAX(salary) AS max_salary
        FROM employee
        GROUP BY departmentId
    ) a ON e.departmentId = a.departmentId
    	AND e.salary = a.max_salary -- max_salary와 같은 경우에 대해서만 INNER JOIN으로 필터링
    INNER JOIN Department d
        ON e.departmentId = d.id

 

 

1. WHERE절 서브쿼리

-- 2. max_salary와 동일한 사람을 찾음
SELECT d.name AS Department,
    e.name AS Employee,
    e.salary AS salary
FROM employee e
    INNER JOIN Department d ON e.departmentId = d.id
WHERE (e.departmentid, e.salary) IN (
    SELECT departmentId,
        MAX(salary) AS max_salary
    FROM employee
    GROUP BY departmentId) -- 1. departmentId별로 가장 잘 버는 Salary를 max_salary라고 함

 

 

2. window function : MAX

SELECT e.name,
    e.salary,
    d.name,
    MAX(Salary) OVER (PARTITION BY departmentid) max_salary -- department별 max salary
FROM Employee e
    INNER JOIN Department d ON e.departmentId = d.id

 

SELECT ms.department,
    ms.name AS employee,
    ms.salary
FROM (
    SELECT e.name,
        e.salary,
        d.name AS department,
        MAX(Salary) OVER (PARTITION BY departmentid) max_salary -- department별 max salary
    FROM Employee e
        INNER JOIN Department d ON e.departmentId = d.id
) ms
WHERE ms.salary = ms.max_salary

 

 

3. window function : RANK()

department별 1등 salary를 window function을 이용해서 구해준다 !

-- department별 1등 salary
SELECT department, employee, salary
FROM (
    SELECT d.name AS department,
        e.name AS employee,
        e.salary,
        RANK() OVER (PARTITION BY e.departmentid ORDER BY salary DESC) AS rnk
    FROM employee e
        INNER JOIN department d ON e.departmentId = d.id
) sub
WHERE rnk = 1