ソースコード
SELECT
    2020 AS SV_YEAR
    , PF_NAME AS PREFECTURE
    , KIND
    , AMT
FROM
(
    SELECT
        PF_CODE
        , '小学校' AS KIND
        , SUM(ELEMENTARY) AS AMT
        , 1 AS VIEW
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        PF_CODE
    HAVING
        AMT IS NOT NULL
    UNION ALL
    SELECT
        PF_CODE
        , '中学校' AS KIND
        , SUM(MIDDLE) AS AMT
        , 2 AS VIEW
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        PF_CODE
    HAVING
        AMT IS NOT NULL
    UNION ALL
    SELECT
        PF_CODE
        , '高校' AS KIND
        , SUM(HIGH) AS AMT
        , 3 AS VIEW
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        PF_CODE
    HAVING
        AMT IS NOT NULL
    UNION ALL
    SELECT
        PF_CODE
        , '短大' AS KIND
        , SUM(JUNIOR_CLG) AS AMT
        , 4 AS VIEW
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        PF_CODE
    HAVING
        AMT IS NOT NULL
    UNION ALL
    SELECT
        PF_CODE
        , '大学' AS KIND
        , SUM(COLLEGE) AS AMT
        , 5 AS VIEW
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        PF_CODE
    HAVING
        AMT IS NOT NULL
    UNION ALL
    SELECT
        PF_CODE
        , '大学院' AS KIND
        , SUM(GRADUATE) AS AMT
        , 6 AS VIEW
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        PF_CODE
    HAVING
        AMT IS NOT NULL
) AS MAIN
INNER JOIN PREFECTURE
 ON MAIN.PF_CODE = PREFECTURE.PF_CODE
ORDER BY
    MAIN.PF_CODE
    , VIEW
提出情報
提出日時2022/09/21 20:02:31
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者peter-yusuke
状態 (詳細)AC
(Accepted: 正答)
メモリ使用量105 MB
メッセージ
テストケース(通過数/総数)
2/2
状態
メモリ使用量
データパターン1
AC
91 MB
データパターン2
AC
105 MB