ソースコード
WITH ENROLLMENT_STACK AS (
    SELECT
        E_S.SURVEY_YEAR AS SV_YEAR,
        PF_CODE,
        '小学校' AS KIND,
        SUM(E_S.ELEMENTARY) AS AMT
    FROM
        ENROLLMENT_STATUS E_S
    WHERE
        E_S.SURVEY_YEAR = 2020
    GROUP BY
        E_S.SURVEY_YEAR,
        PF_CODE
    HAVING
        AMT IS NOT NULL
        
    UNION ALL
    
    SELECT
        E_S.SURVEY_YEAR AS SV_YEAR,
        PF_CODE,
        '中学校' AS KIND,
        SUM(E_S.MIDDLE) AS AMT
    FROM
        ENROLLMENT_STATUS E_S
    WHERE
        E_S.SURVEY_YEAR = 2020
    GROUP BY
        E_S.SURVEY_YEAR,
        PF_CODE
    HAVING
        AMT IS NOT NULL
    
    UNION ALL
    
    SELECT
        E_S.SURVEY_YEAR AS SV_YEAR,
        PF_CODE,
        '高校' AS KIND,
        SUM(E_S.HIGH) AS AMT
    FROM
        ENROLLMENT_STATUS E_S
    WHERE
        E_S.SURVEY_YEAR = 2020
    GROUP BY
        E_S.SURVEY_YEAR,
        PF_CODE
    HAVING
        AMT IS NOT NULL
    
    UNION ALL
    
    SELECT
        E_S.SURVEY_YEAR AS SV_YEAR,
        PF_CODE,
        '短大' AS KIND,
        SUM(E_S.JUNIOR_CLG) AS AMT
    FROM
        ENROLLMENT_STATUS E_S
    WHERE
        E_S.SURVEY_YEAR = 2020
    GROUP BY
        E_S.SURVEY_YEAR,
        PF_CODE
    HAVING
        AMT IS NOT NULL
        
    UNION ALL
    
    SELECT
        E_S.SURVEY_YEAR AS SV_YEAR,
        PF_CODE,
        '大学' AS KIND,
        SUM(E_S.COLLEGE) AS AMT
    FROM
        ENROLLMENT_STATUS E_S
    WHERE
        E_S.SURVEY_YEAR = 2020
    GROUP BY
        E_S.SURVEY_YEAR,
        PF_CODE
    HAVING
        AMT IS NOT NULL
        
    UNION ALL
    
    SELECT
        E_S.SURVEY_YEAR AS SV_YEAR,
        PF_CODE,
        '大学院' AS KIND,
        SUM(E_S.GRADUATE) AS AMT
    FROM
        ENROLLMENT_STATUS E_S
    WHERE
        E_S.SURVEY_YEAR = 2020
    GROUP BY
        E_S.SURVEY_YEAR,
        PF_CODE
    HAVING
        AMT IS NOT NULL
)
SELECT
    SV_YEAR,
    P.PF_NAME AS PREFECTURE,
    KIND,
    AMT
FROM
    ENROLLMENT_STACK E_S
    INNER JOIN PREFECTURE P
        ON P.PF_CODE = E_S.PF_CODE
ORDER BY
    P.PF_CODE ASC
;
提出情報
提出日時2022/09/22 00:52:48
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者Maximum-Likelihood-Engineer
状態 (詳細)AC
(Accepted: 正答)
メモリ使用量99 MB
メッセージ
テストケース(通過数/総数)
2/2
状態
メモリ使用量
データパターン1
AC
99 MB
データパターン2
AC
85 MB