[SQL문제풀기] 연도별 대장균 크기의 편차 구하기

문제

내가 작성한 오답

SELECT L.YEAR, 최대-L.SIZE_OF_COLONY YEAR_DEV, L.ID 
FROM (SELECT DATE_FORMAT(DIFFERENTIATION_DATE,'%Y') YEAR, ID, SIZE_OF_COLONY
        FROM ECOLI_DATA) L
JOIN (SELECT DATE_FORMAT(DIFFERENTIATION_DATE,'%Y') YEAR, MAX(SIZE_OF_COLONY) 최대
    FROM ECOLI_DATA
    GROUP BY DATE_FORMAT(DIFFERENTIATION_DATE,'%Y')) R ON L.YEAR = R.YEAR
ORDER BY L.YEAR ASC, YEAR_DEV ASC;

수정한 정답

SELECT CAST(L.YEAR AS UNSIGNED) AS YEAR, 최대-CAST(L.SIZE_OF_COLONY AS UNSIGNED) YEAR_DEV, L.ID 
FROM (SELECT DATE_FORMAT(DIFFERENTIATION_DATE,'%Y') YEAR, ID, SIZE_OF_COLONY
        FROM ECOLI_DATA) L
JOIN (SELECT DATE_FORMAT(DIFFERENTIATION_DATE,'%Y') YEAR, MAX(SIZE_OF_COLONY) 최대
    FROM ECOLI_DATA
    GROUP BY DATE_FORMAT(DIFFERENTIATION_DATE,'%Y')) R ON L.YEAR = R.YEAR
ORDER BY CAST(L.YEAR AS UNSIGNED) ASC, YEAR_DEV ASC;