[SQL] HackerRank - Project Planning

문제: 여기

넘 어렵다 ... ㅠ_ㅠ
도저히 감이 안 잡혀서 남의 답안 훔쳐보기 ~ !!

SET sql_mode = '';
SELECT Start_Date, End_Date
FROM 
    (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) a,
    (SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) b 
WHERE Start_Date < End_Date
GROUP BY Start_Date 
ORDER BY DATEDIFF(End_Date, Start_Date), Start_Date

SET sql_mode = ''; 를 맨 위에 쓴 이유는
ONLY_FULL_GROUP_BY 때문임

MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them.

그래서 ONLY_FULL_GROUP_BY 를 비활성화 시키는 거다.
근데 온리풀그룹바이를 풀면

In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want.

라는데 ... value 값이 내가 원하는 바랑 다르게 나올 수 있어서 이렇게 풀기엔 뭔가 위험해 보이긴 한다...

그래서 JOIN 을 써서 푸는 방법 ~ !

select a.start_date, b.end_date
from
(select start_date, row_number() over (order by start_date) rn
 from Projects
 where start_date not in (select end_date from Projects)) a
left join 
(select end_date, row_number() over (order by end_date) rn
 from Projects
 where end_date not in (select start_date from Projects)) b
on a.rn=b.rn
order by Datediff(day, a.start_date, b.end_date), a.start_date

ROW_NUMBER() over_clause -> order by 와 함께 쓰면 정렬한 후에 번호 매기기


아래 방법이 개인적으로 더 맘에 든당 o(￣▽￣)ｄ