ソースコード
WITH TOTAL AS (
				SELECT
					SURVEY_YEAR,
					ES.PF_CODE,
					PF_NAME,
					SUM(IFNULL(ELEMENTARY, 0)) AS ELEM,
					SUM(IFNULL(MIDDLE, 0)) AS MIDDLE,
					SUM(IFNULL(HIGH, 0)) AS HIGH,
					SUM(IFNULL(JUNIOR_CLG, 0)) AS JUNIOR_CLG,
					SUM(IFNULL(COLLEGE, 0)) AS COLLEGE,
					SUM(IFNULL(GRADUATE, 0)) 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:41:42
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者tanaka
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量99 MB
メッセージ
テストケース(通過数/総数)
1/2
状態
メモリ使用量
データパターン1
WA
98 MB
データパターン2
AC
99 MB