ソースコード
select SURVEY_YEAR as SV_YEAR
, PF_NAME as PREFECTURE
, KIND
, AMT
from (
select e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE
,1 as sk, "小学校" as KIND, sum(e.ELEMENTARY) as AMT
from ENROLLMENT_STATUS e
     inner join PREFECTURE p
       on p.PF_CODE = e.PF_CODE
where e.SURVEY_YEAR = 2020 and e.ELEMENTARY is not null
group by e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE

union all
select e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE
,2 as sk, "中学校" as KIND, sum(e.MIDDLE) as AMT
from ENROLLMENT_STATUS e
     inner join PREFECTURE p
       on p.PF_CODE = e.PF_CODE
where e.SURVEY_YEAR = 2020 and e.MIDDLE is not null
group by e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE

union all
select e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE
,3 as sk, "高校" as KIND, sum(e.HIGH) as AMT
from ENROLLMENT_STATUS e
     inner join PREFECTURE p
       on p.PF_CODE = e.PF_CODE
where e.SURVEY_YEAR = 2020 and e.HIGH is not null
group by e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE

union all
select e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE
,4 as sk, "短大" as KIND, sum(e.JUNIOR_CLG) as AMT
from ENROLLMENT_STATUS e
     inner join PREFECTURE p
       on p.PF_CODE = e.PF_CODE
where e.SURVEY_YEAR = 2020 and e.JUNIOR_CLG is not null
group by e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE

union all
select e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE
,5 as sk, "大学" as KIND, sum(e.COLLEGE) as AMT
from ENROLLMENT_STATUS e
     inner join PREFECTURE p
       on p.PF_CODE = e.PF_CODE
where e.SURVEY_YEAR = 2020 and e.COLLEGE is not null
group by e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE

union all
select e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE
,6 as sk, "大学院" as KIND, sum(e.GRADUATE) as AMT
from ENROLLMENT_STATUS e
     inner join PREFECTURE p
       on p.PF_CODE = e.PF_CODE
where e.SURVEY_YEAR = 2020 and e.GRADUATE is not null
group by e.SURVEY_YEAR, p.PF_NAME, p.PF_CODE
)
order by PF_CODE
, sk
提出情報
提出日時2022/09/22 00:26:29
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者kiharu
状態 (詳細)AC
(Accepted: 正答)
メモリ使用量96 MB
メッセージ
テストケース(通過数/総数)
2/2
状態
メモリ使用量
データパターン1
AC
96 MB
データパターン2
AC
94 MB