TOPSIC SQL CONTEST

ソースコード

SELECT SURVEY_YEAR AS SV_YEAR,PF_NAME AS PREFECTURE,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:36:33
コンテスト	第2回 SQLコンテスト
問題	就学状況の表示変換
受験者	Clover
状態 (詳細)	AC (Accepted: 正答)
メモリ使用量	107 MB

メッセージ

テストケース(通過数／総数)

2/2

状態

メモリ使用量

データパターン1

107 MB

データパターン2

94 MB