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