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

silver

Jun 05, 2025

Contents

문제 MYSQL

문제

school.programmers.co.kr

https://school.programmers.co.kr/learn/courses/30/lessons/299310

MYSQL

내가 작성한 오답

: YEAR에 출력되는 값이 문자열이 아닌 숫자여야 했다.


WITH A AS (SELECT DATE_FORMAT(DIFFERENTIATION_DATE,'%Y') YEAR, MAX(SIZE_OF_COLONY) YM
            FROM ECOLI_DATA
            GROUP BY YEAR)
SELECT A.YEAR, (A.YM-E.SIZE_OF_COLONY) YEAR_DEV, E.ID
FROM ECOLI_DATA E
JOIN A A ON DATE_FORMAT(E.DIFFERENTIATION_DATE,'%Y') = A.YEAR
ORDER BY 1,2;

내가 작성한 정답

: +0을 해서 숫자로 만들어줬다


WITH A AS (SELECT DATE_FORMAT(DIFFERENTIATION_DATE,'%Y')+0 YEAR, MAX(SIZE_OF_COLONY) YM
            FROM ECOLI_DATA
            GROUP BY YEAR)
SELECT A.YEAR, (A.YM-E.SIZE_OF_COLONY) YEAR_DEV, E.ID
FROM ECOLI_DATA E
JOIN A A ON DATE_FORMAT(E.DIFFERENTIATION_DATE,'%Y') = A.YEAR
ORDER BY 1,2;