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