[MYSQL 37] 분기별 분화된 대장균의 개체 수 구하기

분기별 분화된 대장균의 개체 수 구하기 (LEVEL3)

각 분기(QUARTER)별 분화된 대장균의 개체의 총 수(ECOLI_COUNT)를 출력 (각 분기에는 'Q' )

정렬  분기 오름차순으로 해주세요. (대장균 개체가 분화되지 않은 분기는 없습니다.)

---

정답 코드

SELECT (CASE 
        WHEN (MONTH >= 1 AND MONTH <= 3) THEN "1Q" 
        WHEN (MONTH >= 4 AND MONTH <= 6) THEN "2Q" 
        WHEN (MONTH >= 7 AND MONTH <= 9) THEN "3Q" 
        ELSE "4Q" 
        END) AS QUARTER, count(*) as ECOLI_COUNT
FROM (SELECT DATE_FORMAT(DIFFERENTIATION_DATE, "%m") AS MONTH
      FROM ECOLI_DATA) AS A
GROUP BY QUARTER
ORDER BY QUARTER

 

다른 정답 코드

SELECT (CASE 
        WHEN (MONTH(DIFFERENTIATION_DATE) >= 1 AND MONTH(DIFFERENTIATION_DATE) <= 3) THEN "1Q" 
        WHEN (MONTH(DIFFERENTIATION_DATE) >= 4 AND MONTH(DIFFERENTIATION_DATE) <= 6) THEN "2Q" 
        WHEN (MONTH(DIFFERENTIATION_DATE) >= 7 AND MONTH(DIFFERENTIATION_DATE) <= 9) THEN "3Q" 
        ELSE "4Q" 
        END) AS QUARTER, 
       COUNT(*) AS ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY QUARTER
ORDER BY QUARTER;

SELECT (CASE 
        WHEN (MONTH(DIFFERENTIATION_DATE) IN ('01','02','03') THEN "1Q" 
        WHEN (MONTH(DIFFERENTIATION_DATE) IN ('04','05','06') THEN "2Q" 
        WHEN (MONTH(DIFFERENTIATION_DATE) IN ('07','08','09') THEN "3Q" 
        ELSE "4Q" 
        END) AS QUARTER, 
       COUNT(*) AS ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY QUARTER
ORDER BY QUARTER;

 

 

MEMO

• 아래와 같은 코드를 작성했는데 제대로 출력되지 않음. FROM 에서 건너받은 이름도 QUARTER 여서 충돌

SELECT (CASE 
        WHEN (QUARTER >= 1 AND QUARTER <= 3) THEN "1Q" 
        WHEN (QUARTER >= 4 AND QUARTER <= 6) THEN "2Q" 
        WHEN (QUARTER >= 7 AND QUARTER <= 9) THEN "3Q" 
        ELSE "4Q" 
        END) AS QUARTER, count(*) as ECOLI_COUNT
FROM (SELECT DATE_FORMAT(DIFFERENTIATION_DATE, "%m") AS QUARTER	
      FROM ECOLI_DATA) AS A
GROUP BY QUARTER
ORDER BY QUARTER