ソースコード
SELECT 
     T.SV_YEAR
    ,T.PREFECTURE
    ,T.KIND
    ,T.AMT
FROM(
SELECT 
     ES.SURVEY_YEAR AS SV_YEAR
    ,ES.PF_CODE
    ,PR.PF_NAME AS PREFECTURE
    ,'小学校' AS KIND
    ,SUM(IFNULL(ES.ELEMENTARY,0)) AS AMT
    ,1 AS SEQ
FROM ENROLLMENT_STATUS ES
INNER JOIN PREFECTURE PR
    ON ES.PF_CODE = PR.PF_CODE
WHERE ES.SURVEY_YEAR = 2020
GROUP BY ES.SURVEY_YEAR,ES.PF_CODE
UNION ALL
SELECT 
     ES.SURVEY_YEAR AS SV_YEAR
    ,ES.PF_CODE
    ,PR.PF_NAME AS PREFECTURE
    ,'中学校' AS KIND
    ,SUM(IFNULL(ES.MIDDLE,0)) AS AMT
    ,2 AS SEQ
FROM ENROLLMENT_STATUS ES
INNER JOIN PREFECTURE PR
    ON ES.PF_CODE = PR.PF_CODE
WHERE ES.SURVEY_YEAR = 2020
GROUP BY ES.SURVEY_YEAR,ES.PF_CODE
UNION ALL
SELECT 
     ES.SURVEY_YEAR AS SV_YEAR
    ,ES.PF_CODE
    ,PR.PF_NAME AS PREFECTURE
    ,'高校' AS KIND
    ,SUM(IFNULL(ES.HIGH,0)) AS AMT
    ,3 AS SEQ
FROM ENROLLMENT_STATUS ES
INNER JOIN PREFECTURE PR
    ON ES.PF_CODE = PR.PF_CODE
WHERE ES.SURVEY_YEAR = 2020
GROUP BY ES.SURVEY_YEAR,ES.PF_CODE
UNION ALL
SELECT 
     ES.SURVEY_YEAR AS SV_YEAR
    ,ES.PF_CODE
    ,PR.PF_NAME AS PREFECTURE
    ,'短大' AS KIND
    ,SUM(IFNULL(ES.JUNIOR_CLG,0)) AS AMT
    ,4 AS SEQ
FROM ENROLLMENT_STATUS ES
INNER JOIN PREFECTURE PR
    ON ES.PF_CODE = PR.PF_CODE
WHERE ES.SURVEY_YEAR = 2020
GROUP BY ES.SURVEY_YEAR,ES.PF_CODE
UNION ALL
SELECT 
     ES.SURVEY_YEAR AS SV_YEAR
    ,ES.PF_CODE
    ,PR.PF_NAME AS PREFECTURE
    ,'大学' AS KIND
    ,SUM(IFNULL(ES.COLLEGE,0)) AS AMT
    ,5 AS SEQ
FROM ENROLLMENT_STATUS ES
INNER JOIN PREFECTURE PR
    ON ES.PF_CODE = PR.PF_CODE
WHERE ES.SURVEY_YEAR = 2020
GROUP BY ES.SURVEY_YEAR,ES.PF_CODE
UNION ALL
SELECT 
     ES.SURVEY_YEAR AS SV_YEAR
    ,ES.PF_CODE
    ,PR.PF_NAME AS PREFECTURE
    ,'大学院' AS KIND
    ,SUM(IFNULL(ES.GRADUATE,0)) AS AMT
    ,6 AS SEQ
FROM ENROLLMENT_STATUS ES
INNER JOIN PREFECTURE PR
    ON ES.PF_CODE = PR.PF_CODE
WHERE ES.SURVEY_YEAR = 2020
GROUP BY ES.SURVEY_YEAR,ES.PF_CODE
) AS T
ORDER BY T.PF_CODE ASC
   , T.SEQ
提出情報
提出日時2022/09/21 20:01:17
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者n_takeda
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量105 MB
メッセージ
テストケース(通過数/総数)
0/2
状態
メモリ使用量
データパターン1
WA
105 MB
データパターン2
WA
88 MB