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