ソースコード
SELECT
    SURVEY_YEAR AS SV_YEAR
  , PREFECTURE.PF_NAME AS PREFECTURE
  , KIND
  , SUM(AMT_BUF) AS AMT
FROM 
  (SELECT SURVEY_YEAR, PF_CODE, 1 AS KIND_ID, "小学校" AS KIND, ELEMENTARY AS AMT_BUF FROM ENROLLMENT_STATUS
   UNION ALL
   SELECT SURVEY_YEAR, PF_CODE, 2 AS KIND_ID, "中学校" AS KIND, MIDDLE AS AMT_BUF FROM ENROLLMENT_STATUS
   UNION ALL
   SELECT SURVEY_YEAR, PF_CODE, 3 AS KIND_ID, "高校" AS KIND, HIGH AS AMT_BUF FROM ENROLLMENT_STATUS
   UNION ALL
   SELECT SURVEY_YEAR, PF_CODE, 4 AS KIND_ID, "短大" AS KIND, JUNIOR_CLG AS AMT_BUF FROM ENROLLMENT_STATUS
   UNION ALL
   SELECT SURVEY_YEAR, PF_CODE, 5 AS KIND_ID, "大学" AS KIND, COLLEGE AS AMT_BUF FROM ENROLLMENT_STATUS
   UNION ALL
   SELECT SURVEY_YEAR, PF_CODE, 6 AS KIND_ID, "大学院" AS KIND, GRADUATE AS AMT_BUF FROM ENROLLMENT_STATUS
  ) ES2
  INNER JOIN PREFECTURE
     ON PREFECTURE.PF_CODE = ES2.PF_CODE
WHERE
       ES2.SURVEY_YEAR = 2020
   AND ES2.AMT_BUF IS NOT NULL
GROUP BY
    ES2.SURVEY_YEAR
  , ES2.PF_CODE
  , ES2.KIND
ORDER BY
    ES2.PF_CODE ASC
  , ES2.KIND_ID ASC
提出情報
提出日時2024/03/29 16:52:02
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者KK
状態 (詳細)AC
(Accepted: 正答)
メモリ使用量85 MB
メッセージ
テストケース(通過数/総数)
2/2
状態
メモリ使用量
データパターン1
AC
85 MB
データパターン2
AC
84 MB