[프로그래머스] Lv.1 연도별 대장균 크기의 편차 구하기

 

풀이 Tip

left join으로 풀기

 

select b.years as 'YEAR', (b.maxSize - a.SIZE_OF_COLONY) as 'YEAR_DEV', a.ID
from ECOLI_DATA  a
left join (select max(SIZE_OF_COLONY) as 'maxSize', year(DIFFERENTIATION_DATE) as 'years'
           from ECOLI_DATA 
           group by year(DIFFERENTIATION_DATE) ) b
on  year(a.DIFFERENTIATION_DATE) = b.years
order by YEAR asc, YEAR_DEV asc;