ソースコード
with syo as (
 select PF_CODE,SURVEY_YEAR,ELEMENTARY as AMT,'小学校' as KIND, 1 as odr
 from ENROLLMENT_STATUS
 where SURVEY_YEAR = 2020
 group by PF_CODE
)
,chu as (
 select PF_CODE,SURVEY_YEAR,MIDDLE as AMT,'中学校' as KIND, 2 as odr
 from ENROLLMENT_STATUS
 where SURVEY_YEAR = 2020
 group by PF_CODE
)
,koko as (
 select PF_CODE,SURVEY_YEAR,HIGH as AMT,'高校' as KIND, 3 as odr
 from ENROLLMENT_STATUS
 where SURVEY_YEAR = 2020
 group by PF_CODE
)
,tan as (
 select PF_CODE,SURVEY_YEAR,JUNIOR_CLG as AMT,'短大' as KIND, 4 as odr
 from ENROLLMENT_STATUS
 where SURVEY_YEAR = 2020
 group by PF_CODE
)
,dai as (
 select PF_CODE,SURVEY_YEAR,COLLEGE as AMT,'大学' as KIND, 5 as odr
 from ENROLLMENT_STATUS
 where SURVEY_YEAR = 2020
 group by PF_CODE
)
,daiga as (
 select PF_CODE,SURVEY_YEAR,GRADUATE as AMT,'大学院' as KIND, 6 as odr
 from ENROLLMENT_STATUS
 where SURVEY_YEAR = 2020
 group by PF_CODE
)
select 
   kin.SURVEY_YEAR as SV_YEAR
 ,pr.PF_NAME as PREFECTURE
 ,kin.kind as KIND
 ,kin.AMT as AMT
from 
  PREFECTURE pr
,(
  select * from syo union all
  select * from chu union all
  select * from koko union all
  select * from tan union all
  select * from dai union all
  select * from daiga
) kin
where 
pr.PF_CODE = kin.PF_CODE
and (AMT is not null and AMT > 0)
order by 
  pr.PF_CODE
 ,kin.odr
;
提出情報
提出日時2022/09/21 22:18:51
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者idakazoo
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量102 MB
メッセージ
テストケース(通過数/総数)
0/2
状態
メモリ使用量
データパターン1
WA
102 MB
データパターン2
WA
101 MB