ソースコード
WITH ELEMENTARY as (
    SELECT PR.PF_CODE
          ,SURVEY_YEAR AS SV_YEAR
          ,PF_NAME
          ,SUM(ELEMENTARY) AS AMT
          ,'小学校' AS KIND
          ,1 AS SRT
      FROM ENROLLMENT_STATUS ES
      LEFT OUTER JOIN PREFECTURE PR
        ON PR.PF_CODE = ES.PF_CODE
     WHERE SURVEY_YEAR = '2020'
     GROUP BY SURVEY_YEAR, PR.PF_CODE, PR.PF_NAME
   ),
   MIDDLE as (
    SELECT PR.PF_CODE
          ,SURVEY_YEAR AS SV_YEAR
          ,PF_NAME
          ,SUM(MIDDLE) AS AMT
          ,'中学校' AS KIND
          ,2 AS SRT
      FROM ENROLLMENT_STATUS ES
      LEFT OUTER JOIN PREFECTURE PR
        ON PR.PF_CODE = ES.PF_CODE
     WHERE SURVEY_YEAR = '2020'
     GROUP BY SURVEY_YEAR, PR.PF_CODE, PR.PF_NAME
   ),
   HIGH as (
    SELECT PR.PF_CODE
          ,SURVEY_YEAR AS SV_YEAR
          ,PF_NAME
          ,SUM(HIGH) AS AMT
          ,'高校' AS KIND
          ,3 AS SRT
      FROM ENROLLMENT_STATUS ES
      LEFT OUTER JOIN PREFECTURE PR
        ON PR.PF_CODE = ES.PF_CODE
     WHERE SURVEY_YEAR = '2020'
     GROUP BY SURVEY_YEAR, PR.PF_CODE, PR.PF_NAME
   ),
   JUNIOR_CLG as (
    SELECT PR.PF_CODE
          ,SURVEY_YEAR AS SV_YEAR
          ,PF_NAME
          ,SUM(JUNIOR_CLG) AS AMT
          ,'短大' AS KIND
          ,4 AS SRT
      FROM ENROLLMENT_STATUS ES
      LEFT OUTER JOIN PREFECTURE PR
        ON PR.PF_CODE = ES.PF_CODE
     WHERE SURVEY_YEAR = '2020'
     GROUP BY SURVEY_YEAR, PR.PF_CODE, PR.PF_NAME
   ),
   COLLEGE as (
    SELECT PR.PF_CODE
          ,SURVEY_YEAR AS SV_YEAR
          ,PF_NAME
          ,SUM(COLLEGE) AS AMT
          ,'大学' AS KIND
          ,5 AS SRT
      FROM ENROLLMENT_STATUS ES
      LEFT OUTER JOIN PREFECTURE PR
        ON PR.PF_CODE = ES.PF_CODE
     WHERE SURVEY_YEAR = '2020'
     GROUP BY SURVEY_YEAR, PR.PF_CODE, PR.PF_NAME
   ),
   GRADUATE as (
    SELECT PR.PF_CODE
          ,SURVEY_YEAR AS SV_YEAR
          ,PF_NAME
          ,SUM(GRADUATE) AS AMT
          ,'大学院' AS KIND
          ,6 AS SRT
      FROM ENROLLMENT_STATUS ES
      LEFT OUTER JOIN PREFECTURE PR
        ON PR.PF_CODE = ES.PF_CODE
     WHERE SURVEY_YEAR = '2020'
     GROUP BY SURVEY_YEAR, PR.PF_CODE, PR.PF_NAME
   )
SELECT SV_YEAR
      ,PF_NAME AS PREFECTURE
      ,KIND
      ,AMT
FROM (
SELECT 
       PF_CODE
      ,SV_YEAR
      ,PF_NAME
      ,KIND
      ,AMT 
      ,SRT
  FROM ELEMENTARY
 WHERE AMT IS NOT NULL
UNION ALL
SELECT PF_CODE
      ,SV_YEAR
      ,PF_NAME
      ,KIND
      ,AMT 
      ,SRT
  FROM MIDDLE
 WHERE AMT IS NOT NULL
UNION ALL
SELECT PF_CODE
      ,SV_YEAR
      ,PF_NAME
      ,KIND
      ,AMT 
      ,SRT
  FROM HIGH
 WHERE AMT IS NOT NULL
UNION ALL
SELECT PF_CODE
      ,SV_YEAR
      ,PF_NAME
      ,KIND
      ,AMT 
      ,SRT
  FROM JUNIOR_CLG
 WHERE AMT IS NOT NULL
UNION ALL
SELECT PF_CODE
      ,SV_YEAR
      ,PF_NAME
      ,KIND
      ,AMT 
      ,SRT
  FROM COLLEGE
 WHERE AMT IS NOT NULL
UNION ALL
SELECT PF_CODE
      ,SV_YEAR
      ,PF_NAME
      ,KIND
      ,AMT 
      ,SRT
  FROM GRADUATE
 WHERE AMT IS NOT NULL
ORDER BY PF_CODE, SRT
)
提出情報
提出日時2022/09/21 18:57:49
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者yakiniku_tabetai_pakupaku
状態 (詳細)AC
(Accepted: 正答)
メモリ使用量105 MB
メッセージ
テストケース(通過数/総数)
2/2
状態
メモリ使用量
データパターン1
AC
105 MB
データパターン2
AC
104 MB