ソースコード
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