[Leetcode] Database (MySQL) 문제 풀이 - 1

175. Combine Two Tables

select p.firstName, p.lastName, a.city, a.state 
from Person p
left join Address a on a.personId = p.personId;

176. Second Highest Salary

• 2번째로 큰 salary를 조회하라.
• 2번째로 큰 salary가 없다면 null을 출력하라.

select max(salary) as 'SecondHighestSalary'
from Employee
where salary < (select max(salary) from Employee);

where절 서브쿼리로 salary의 최대값을 가져오고 가져온 최대값보다 작은 salary 중 최대값을 출력한다.

177. Nth Highest Salary

• N번째 큰 salary를 조회하라

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N = N-1;
  RETURN (     
     select distinct salary
      from Employee
      order by salary desc
      limit 1
      offset N
  );
END

스토어드 함수가 사용된 처음 보는 문제유형이다.
N번째 큰 값을 가져오는 것은 여러가지 방법이 있겠지만 limit-offset방법을 선택했다.

offset N을 사용할 때 offset N-1과 같은 방식은 에러가 발생한다.
앞서 미리 offset으로 사용하기 위해 N을 감소시키고 사용했다.

SET N = N-1;

177. Rank Scores

• score에 대한 내림차순 기준 랭킹을 구하라
• 공동순위를 허용한다. (1 -> 1 -> 2 -> 3 ...)

select score, dense_rank() over (order by score desc) as 'rank'
from Scores;

MySQL에서 rank()함수를 사용하는 기본적인 방법은 아래와 같다.

select rank() over (order by 기준칼럼 [asc|desc])

select dense_rank() over (order by 기준칼럼 [asc|desc])

일반적인 rank()의 경우 같은 값에 대해 공동순위를 갖지 않는다.
만약 공동순위를 허용하고자 한다면 dense_rank()를 사용한다.

181. Employees Earning More Than Their Managers

• manager보다 높은 salary를 받는 직원을 조회하라
• 한 개 테이블에 직원에 대한 정보와 매니저에 대한 정보가 모두 들어있다.
  • managerId는 다른 row의 pk를 참조하는 형태이다.

select e1.name as 'Employee'
from Employee e1
join Employee e2 on e1.managerId = e2.id
where e1.salary > e2.salary;

한 개 테이블에서 join을 하는 self join으로 문제를 해결했다.
e1은 직원에 해당하고 e2는 매니저에 해당한다.

182. Duplicate Emails

• email이 2번 이상 중복된 email을 조회하라.

select email
from Person
group by email
having count(email) > 1;

email로 그룹핑 후 having절을 이용해서 그룹핑 된 결과 count가 1보다 큰 것만 조회한다.

183. Customers Who Never Order

• Customers테이블에는 있지만 Order 테이블에는 없는 Customer의 name을 조회하라
• 주문을 하지 않은 고객

1. 주문한 고객을 조회 후 not in 사용

select name as 'Customers'
from Customers
where id not in (
        select c.id
        from Customers c
        join Orders o on c.id = o.customerId
    );

2. 모든 고객을 주문과 함께 조회(left join) 후 customerId가 있는지 확인

select name as 'Customers'
from Customers c
left join Orders o on c.id = o.customerId
where o.customerId is null;

184. Department Highest Salary

• 각 부서별로 가장 높은 연봉을 받는 직원의 부서, 이름, 연봉을 조회하라
• 각 부서별 최대 연봉 동률이 있다면 모두 출력한다.

select d.name as 'Department', e.name as 'Employee', e.salary as 'Salary'
from Employee e
join Department d on e.departmentId = d.id
where (e.departmentId, e.salary) in
    (select departmentId, max(salary)
     from Employee
     group by departmentId
    );

부서별 가장 높은 연봉을 받는다 라는 조건을 만들기 위해 where절 서브쿼리에서 그룹화한다.