コンテストの制限時間が終了しました。
以降も提出を行うことができますが、順位集計には反映されません。
以降も提出を行うことができますが、順位集計には反映されません。
ソースコード
with tmp as (
select survey_year as SV_YEAR,pf_code,'1' as NO,'elementary' as KIND,sum(elementary) as AMT from enrollment_status
where survey_year=2020
and elementary is not null
group by pf_code
union all
select survey_year as SV_YEAR,pf_code,'2' as NO,'middle' as KIND,sum(middle) as AMT from enrollment_status
where survey_year=2020
and middle is not null
group by pf_code
union all
select survey_year as SV_YEAR,pf_code,'3' as NO,'high' as KIND,sum(high) as AMT from enrollment_status
where survey_year=2020
and high is not null
group by pf_code
union all
select survey_year as SV_YEAR,pf_code,'4' as NO,'junior_clg' as KIND,sum(junior_clg) as AMT from enrollment_status
where survey_year=2020
and junior_clg is not null
group by pf_code
union all
select survey_year as SV_YEAR,pf_code,'5' as NO,'college' as KIND,sum(college) as AMT from enrollment_status
where survey_year=2020
and college is not null
group by pf_code
union all
select survey_year as SV_YEAR,pf_code,'6' as NO,'graduate' as KIND,sum(graduate) as AMT from enrollment_status
where survey_year=2020
and graduate is not null
group by pf_code
)
select
SV_YEAR,
pf_name as PREFECTURE,
case kind
when 'elementary' then '小学校'
when 'middle' then '中学校'
when 'high' then '高校'
when 'junior_clg' then '短大'
when 'college' then '大学'
when 'graduate' then '大学院'
end as KIND,
AMT
from tmp
inner join prefecture as p
on tmp.pf_code=p.pf_code
order by tmp.pf_code,no;
提出情報
提出日時 | 2023/09/21 23:08:02 |
コンテスト | 第2回 SQLコンテスト |
問題 | 就学状況の表示変換 |
受験者 | kate |
状態 (詳細) | AC (Accepted: 正答) |
メモリ使用量 | 78 MB |
メッセージ
テストケース(通過数/総数)
2/2
状態
メモリ使用量
データパターン1
AC
78 MB
データパターン2
AC
76 MB