ソースコード
WITH TOTAL AS (
				SELECT
					SURVEY_YEAR,
					ES.PF_CODE,
					PF_NAME,
					SUM(ELEMENTARY) AS ELEM,
					SUM(MIDDLE) AS MIDDLE,
					SUM(HIGH) AS HIGH,
					SUM(JUNIOR_CLG) AS JUNIOR_CLG,
					SUM(COLLEGE) AS COLLEGE,
					SUM(GRADUATE) AS GRADUATE
				FROM
					ENROLLMENT_STATUS ES
					LEFT JOIN PREFECTURE P
					ON ES.PF_CODE = P.PF_CODE
				WHERE
					ES.SURVEY_YEAR = '2020'
				GROUP BY
					SURVEY_YEAR,
					ES.PF_CODE
)
SELECT
	SV_YEAR,
	PREFECTURE,
	KIND,
	AMT
FROM (
SELECT 
	SURVEY_YEAR AS SV_YEAR,
	PF_NAME AS PREFECTURE,
	'小学校' AS KIND,
	ELEM AS AMT,
	PF_CODE,
	1 AS SORT
FROM
	TOTAL
UNION ALL
SELECT 
	SURVEY_YEAR AS SV_YEAR,
	PF_NAME AS PREFECTURE,
	'中学校' AS KIND,
	MIDDLE AS AMT,
	PF_CODE,
	2 AS SORT
FROM
	TOTAL
UNION ALL
SELECT 
	SURVEY_YEAR AS SV_YEAR,
	PF_NAME AS PREFECTURE,
	'高校' AS KIND,
	HIGH AS AMT,
	PF_CODE,
	3 AS SORT
FROM
	TOTAL
UNION ALL
SELECT 
	SURVEY_YEAR AS SV_YEAR,
	PF_NAME AS PREFECTURE,
	'短大' AS KIND,
	JUNIOR_CLG AS AMT,
	PF_CODE,
	4 AS SORT
FROM
	TOTAL
UNION ALL
SELECT 
	SURVEY_YEAR AS SV_YEAR,
	PF_NAME AS PREFECTURE,
	'大学' AS KIND,
	COLLEGE AS AMT,
	PF_CODE,
	5 AS SORT
FROM
	TOTAL
UNION ALL
SELECT 
	SURVEY_YEAR AS SV_YEAR,
	PF_NAME AS PREFECTURE,
	'大学院' AS KIND,
	GRADUATE AS AMT,
	PF_CODE,
	6 AS SORT
FROM
	TOTAL
)
WHERE
    AMT > 0
ORDER BY
	PF_CODE ,
	SORT
提出情報
提出日時2022/09/21 16:48:00
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者tanaka
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量101 MB
メッセージ
テストケース(通過数/総数)
1/2
状態
メモリ使用量
データパターン1
WA
101 MB
データパターン2
AC
100 MB