ソースコード
SELECT SURVEY_YEAR,PF_NAME,KIND,AMT 
FROM
(SELECT SURVEY_YEAR,(SELECT PF_NAME FROM PREFECTURE B WHERE A.PF_CODE = B.PF_CODE) AS PF_NAME,PF_CODE,'小学校' AS KIND,1 AS KINDID,SUM(ELEMENTARY) AS AMT FROM ENROLLMENT_STATUS A WHERE SURVEY_YEAR = 2020 GROUP BY PF_CODE
union
SELECT SURVEY_YEAR,(SELECT PF_NAME FROM PREFECTURE B WHERE A.PF_CODE = B.PF_CODE) AS PF_NAME,PF_CODE,'中学校' AS KIND,2 AS KINDID,SUM(MIDDLE) AS AMT FROM ENROLLMENT_STATUS A WHERE SURVEY_YEAR = 2020 GROUP BY PF_CODE
union
SELECT SURVEY_YEAR,(SELECT PF_NAME FROM PREFECTURE B WHERE A.PF_CODE = B.PF_CODE) AS PF_NAME,PF_CODE,'高校' AS KIND,3 AS KINDID,SUM(HIGH) AS AMT FROM ENROLLMENT_STATUS A WHERE SURVEY_YEAR = 2020 GROUP BY PF_CODE
union
SELECT SURVEY_YEAR,(SELECT PF_NAME FROM PREFECTURE B WHERE A.PF_CODE = B.PF_CODE) AS PF_NAME,PF_CODE,'短大' AS KIND,4 AS KINDID,SUM(JUNIOR_CLG) AS AMT FROM ENROLLMENT_STATUS A WHERE SURVEY_YEAR = 2020 GROUP BY PF_CODE
union
SELECT SURVEY_YEAR,(SELECT PF_NAME FROM PREFECTURE B WHERE A.PF_CODE = B.PF_CODE) AS PF_NAME,PF_CODE,'大学' AS KIND,5 AS KINDID,SUM(COLLEGE) AS AMT FROM ENROLLMENT_STATUS A WHERE SURVEY_YEAR = 2020 GROUP BY PF_CODE
union
SELECT SURVEY_YEAR,(SELECT PF_NAME FROM PREFECTURE B WHERE A.PF_CODE = B.PF_CODE) AS PF_NAME,PF_CODE,'大学院' AS KIND,6 AS KINDID,SUM(GRADUATE) AS AMT FROM ENROLLMENT_STATUS A WHERE SURVEY_YEAR = 2020 GROUP BY PF_CODE)
where AMT IS NOT NULL
order by PF_CODE,KINDID
提出情報
提出日時2022/09/22 11:25:48
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者Clover
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量107 MB
メッセージ
テストケース(通過数/総数)
0/2
状態
メモリ使用量
データパターン1
WA
107 MB
データパターン2
WA
94 MB