[leetcode] Database 문제 풀이 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

# Write your MySQL query statement below
select MAX(e.salary) as SecondHighestSalary from employee e
where e.salary not in (select max(e.salary) from employee e)

or 

select 
    ifnull(
            (select distinct e.salary from employee e
            order by e.salary desc
            limit 1 offset 1), null
        ) as SecondHighestSalary 

177. Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N = N-1;  # Table의 row 0, 1, 2 ... 순서이다.
    RETURN (
        select 
            ifnull(
                    (select distinct e.salary from Employee e
                    order by e.salary desc
                    limit 1 offset N), null
                ) as SecondHighestSalary 
    );
END

178. Rank Scores

select score, DENSE_RANK() over(order by score DESC) as 'rank'
from Scores;

180. Consecutive Numbers

# Write your MySQL query statement below
select distinct l.num AS ConsecutiveNums
from logs l
inner join logs AS l_next on l.id + 1 = l_next.id
inner join logs AS l_next2 on l_next.id + 1 = l_next2.id
where l.num = l_next.num and l_next.num = l_next2.num
# self join 으로 문제를 풀면 3번이상인 수는 어떻게 구분할까?