ソースコード
with base as (
select SURVEY_YEAR,
       PF_CODE,
       "小学校" as KIND,
       total(ELEMENTARY) AMT,
       0 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
and ELEMENTARY is not null
group by SURVEY_YEAR,
       PF_CODE
UNION all 
select SURVEY_YEAR,
       PF_CODE,
       "中学校" as KIND,
       total(MIDDLE)  AMT,
       1 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
and MIDDLE is not null

group by SURVEY_YEAR,
       PF_CODE
UNION all 
select SURVEY_YEAR,
       PF_CODE,
       "高校" as KIND,
       total(HIGH)  AMT,
       2 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
and HIGH is not null

group by SURVEY_YEAR,
       PF_CODE
UNION all 
select SURVEY_YEAR,
       PF_CODE,
       "短大" as KIND,
       total(JUNIOR_CLG) AMT,
       3 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020

and JUNIOR_CLG is not null
group by SURVEY_YEAR,
       PF_CODE
UNION all 
select SURVEY_YEAR,
       PF_CODE,
       "大学" as KIND,
       total(COLLEGE) AMT,
       4 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020

and COLLEGE is not null
group by SURVEY_YEAR,
       PF_CODE
UNION all 
select SURVEY_YEAR,
       PF_CODE,
       "大学院" as KIND,
       total(GRADUATE) AMT,
       5 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020

and GRADUATE is not null
group by SURVEY_YEAR,
       PF_CODE

)
select 
SURVEY_YEAR as SV_YEAR,
PF_NAME as PREFECTURE,
KIND,
AMT
 from base b
 inner join PREFECTURE p on p.pf_code = b.pf_code
order by b.PF_CODE, b.ord
提出情報
提出日時2022/09/21 21:50:02
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者hiraku
状態 (詳細)AC
(Accepted: 正答)
メモリ使用量98 MB
メッセージ
テストケース(通過数/総数)
2/2
状態
メモリ使用量
データパターン1
AC
84 MB
データパターン2
AC
98 MB