LeetCode 문제풀이 (184, 185)

 

# 184

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)
      FROM Employee
      GROUP BY departmentId
    )


# 서브쿼리없이 바로 groupby를 해버리면 안됨. 처음 바로 groupby를 해서 id별 max(salary)를 구하지 못함

where절에 서브쿼리없이 바로 groupby를 해버려서 id별 max(salary)를 구하지못했다. 솔루션의 도움을 받아 해결완료

 

 

 

# 185

# Write your MySQL query statement below
SELECT d.name as 'Department', a.name as 'Employee', a.salary as 'Salary'
FROM (
SELECT *, dense_rank () over (partition by departmentId order by salary desc) as arank 
FROM Employee 
) a 
join Department d 
on a.departmentId = d.id
WHERE arank <= 3;

Hard 난이도지만 무슨일로 dense_rank를 바로 생각해서 어찌저찌 풀어버렸다. 처음엔 계속 where절에 서브쿼리를 두고 풀어서 오류가 났지만 from절안으로 테이블 하나를 만들어서 join해보니 해결되었다.

 

 

추가로 아래와 같이 with나 dense_rank없이도 풀 수 있다는 점을 배웠다.

# with이용
with a as (
select *, dense_rank() over (partition by DepartmentId order by Salary desc) as rank
from Employee)

select d.name as "Department", a.name as "Employee", a.salary as "Salary"
from a a 
join Department d on a.DepartmentId = d.Id
where a.rank < 4


# dense_rank 없이 한 풀이
SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )
;