ソースコード
with
	e as (
		select
			pf_code
			,'ELEMENTARY' as kind
			,sum(elementary) as amt
		from
			enrollment_status
		where
			survey_year = 2020
		group by
			1
		union
		select
			pf_code
			,'MIDDLE' as kind
			,sum(middle) as amt
		from
			enrollment_status
		where
			survey_year = 2020
		group by
			1
		union
		select
			pf_code
			,'HIGH' as kind
			,sum(high) as amt
		from
			enrollment_status
		where
			survey_year = 2020
		group by
			1
		union
		select
			pf_code
			,'JUNIOR_CLG' as kind
			,sum(junior_clg) as amt
		from
			enrollment_status
		where
			survey_year = 2020
		group by
			1
		union
		select
			pf_code
			,'COLLEGE' as kind
			,sum(college) as amt
		from
			enrollment_status
		where
			survey_year = 2020
		group by
			1
		union
		select
			pf_code
			,'GRADUATE' as kind
			,sum(graduate) as amt
		from
			enrollment_status
		where
			survey_year = 2020
		group by
			1
	),
	k as (
		select
			'ELEMENTARY' as kind
		union
		select
			'MIDDLE' as kind
		union
		select
			'HIGH' as kind
		union
		select
			'JUNIOR_CLG' as kind
		union
		select
			'COLLEGE' as kind
		union
		select
			'GRADUATE' as kind
	),
	pk as (
		select
			p.pf_code
			,p.pf_name
			,k.kind
		from
			prefecture p
			cross join k
	)

select
	2020 as SV_YEAR
	,pk.pf_name as PREFECTURE
	,case
		pk.kind
			when 'ELEMENTARY' then '小学校'
			when 'MIDDLE' then '中学校'
			when 'HIGH' then '高校'
			when 'JUNIOR_CLG' then '短大'
			when 'COLLEGE' then '大学'
			when 'GRADUATE' then '大学院'
			end	as KIND
	,e.amt as AMT
from
	pk
	join
		e
		on
			pk.pf_code = e.pf_code
			and pk.kind = e.kind
where
	amt is not null
order by
	pk.pf_code asc
	,case
		pk.kind
			when 'ELEMENTARY' then 1
			when 'MIDDLE' then 2
			when 'HIGH' then 3
			when 'JUNIOR_CLG' then 4
			when 'COLLEGE' then 5
			when 'GRADUATE' then 6
			end
提出情報
提出日時2022/09/21 18:42:13
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者takahirostone
状態 (詳細)AC
(Accepted: 正答)
メモリ使用量102 MB
メッセージ
テストケース(通過数/総数)
2/2
状態
メモリ使用量
データパターン1
AC
98 MB
データパターン2
AC
102 MB