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