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