ソースコード
with base as (
select SURVEY_YEAR,PF_CODE,1 as sk,"小学校" as name,  ELEMENTARY as AMT from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 and ELEMENTARY is not null union all
select SURVEY_YEAR,PF_CODE,2 as sk,"中学校" as name,  MIDDLE as AMT from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 and MIDDLE is not null union all
select SURVEY_YEAR,PF_CODE,3 as sk,"高校" as name,  HIGH as AMT from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 and HIGH is not null union all
select SURVEY_YEAR,PF_CODE,4 as sk,"短大" as name,  JUNIOR_CLG as AMT from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 and JUNIOR_CLG is not null union all
select SURVEY_YEAR,PF_CODE,5 as sk,"大学" as name,  COLLEGE as AMT from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 and COLLEGE is not null union all
select SURVEY_YEAR,PF_CODE,6 as sk,"大学院" as name,  GRADUATE as AMT from ENROLLMENT_STATUS where SURVEY_YEAR = 2020 and GRADUATE is not null
)
select
a.SURVEY_YEAR as 'SV_YEAR',
b.PF_NAME as 'PREFECTURE',
a.name as 'KIND',
sum(a.AMT) as 'AMT'
from base a
inner join PREFECTURE b
on a.PF_CODE=b.PF_CODE
group by SURVEY_YEAR, PF_NAME, name
order by a.PF_CODE,a.sk
提出情報
提出日時2022/09/21 19:39:20
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者mell
状態 (詳細)AC
(Accepted: 正答)
メモリ使用量105 MB
メッセージ
テストケース(通過数/総数)
2/2
状態
メモリ使用量
データパターン1
AC
105 MB
データパターン2
AC
105 MB