ソースコード
SELECT
    ENROLLMENT.SURVEY_YEAR AS SV_YEAR,
    PREFECTURE.PF_NAME AS PREFECTURE,
    ENROLLMENT.KIND AS KIND,
    SUM(ENROLLMENT.AMT) AS AMT
FROM
    (
        SELECT
            PF_CODE,
            SURVEY_YEAR,
            1 as SORT,
            '小学校' AS KIND,
            ELEMENTARY AS AMT
        FROM
            ENROLLMENT_STATUS
        UNION
        SELECT
            PF_CODE,
            SURVEY_YEAR,
            2 as SORT,
            '中学校' AS KIND,
            MIDDLE AS AMT
        FROM
            ENROLLMENT_STATUS
        UNION
        SELECT
            PF_CODE,
            SURVEY_YEAR,
            3 as SORT,
            '高校' AS KIND,
            HIGH AS AMT
        FROM
            ENROLLMENT_STATUS
        UNION
        SELECT
            PF_CODE,
            SURVEY_YEAR,
            4 as SORT,
            '短大' AS KIND,
            JUNIOR_CLG AS AMT
        FROM
            ENROLLMENT_STATUS
        UNION
        SELECT
            PF_CODE,
            SURVEY_YEAR,
            5 as SORT,
            '大学' AS KIND,
            COLLEGE AS AMT
        FROM
            ENROLLMENT_STATUS
        UNION
        SELECT
            PF_CODE,
            SURVEY_YEAR,
            6 as SORT,
            '大学院' AS KIND,
            GRADUATE AS AMT
        FROM
            ENROLLMENT_STATUS
    ) AS ENROLLMENT
    LEFT JOIN PREFECTURE AS PREFECTURE ON 1 = 1
    AND ENROLLMENT.PF_CODE = PREFECTURE.PF_CODE
where
    1 = 1
    AND AMT IS NOT NULL
    AND ENROLLMENT.SURVEY_YEAR = 2020
GROUP BY
    ENROLLMENT.PF_CODE,
    ENROLLMENT.KIND
HAVING
    SUM(ENROLLMENT.AMT) IS NOT NULL
ORDER BY
    ENROLLMENT.PF_CODE,
    ENROLLMENT.SORT
提出情報
提出日時2022/09/21 20:10:26
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者shimarisu
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量93 MB
メッセージ
テストケース(通過数/総数)
0/2
状態
メモリ使用量
データパターン1
WA
82 MB
データパターン2
WA
93 MB