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