ソースコード
select
    SV_YEAR,
    PREFECTURE,
    AMT,
    KIND
from
(  select
    a.SURVEY_YEAR as SV_YEAR,
    b.PF_NAME as PREFECTURE,
    sum(a.ELEMENTARY) as AMT,
    '小学校' as KIND,
    1 as NUM
  from
    ENROLLMENT_STATUS a,
    PREFECTURE b
  where
    a.PF_CODE = b.PF_CODE and
    a.SURVEY_YEAR = 2020 and
    a.ELEMENTARY is not null
  group by
    SV_YEAR, PREFECTURE, KIND
union all
  select
    a.SURVEY_YEAR as SV_YEAR,
    b.PF_NAME as PREFECTURE,
    sum(a.MIDDLE) as AMT,
    '中学校' as KIND,
    2 as NUM
  from
    ENROLLMENT_STATUS a,
    PREFECTURE b
  where
    a.PF_CODE = b.PF_CODE and
    a.SURVEY_YEAR = 2020 and
    a.MIDDLE is not null
  group by
    SV_YEAR, PREFECTURE, KIND
union all
  select
    a.SURVEY_YEAR as SV_YEAR,
    b.PF_NAME as PREFECTURE,
    sum(a.HIGH) as AMT,
    '高校' as KIND,
    3 as NUM
  from
    ENROLLMENT_STATUS a,
    PREFECTURE b
  where
    a.PF_CODE = b.PF_CODE and
    a.SURVEY_YEAR = 2020 and
    a.HIGH is not null
  group by
    SV_YEAR, PREFECTURE, KIND
union all
  select
    a.SURVEY_YEAR as SV_YEAR,
    b.PF_NAME as PREFECTURE,
    sum(a.JUNIOR_CLG) as AMT,
    '短大' as KIND,
    4 as NUM
  from
    ENROLLMENT_STATUS a,
    PREFECTURE b
  where
    a.PF_CODE = b.PF_CODE and
    a.SURVEY_YEAR = 2020 and
    a.JUNIOR_CLG is not null
  group by
    SV_YEAR, PREFECTURE, KIND
union all
  select
    a.SURVEY_YEAR as SV_YEAR,
    b.PF_NAME as PREFECTURE,
    sum(a.COLLEGE) as AMT,
    '大学' as KIND,
    5 as NUM
  from
    ENROLLMENT_STATUS a,
    PREFECTURE b
  where
    a.PF_CODE = b.PF_CODE and
    a.SURVEY_YEAR = 2020 and
    a.COLLEGE is not null
  group by
    SV_YEAR, PREFECTURE, KIND
union all
  select
    a.SURVEY_YEAR as SV_YEAR,
    b.PF_NAME as PREFECTURE,
    sum(a.GRADUATE) as AMT,
    '大学院' as KIND,
    6 as NUM
  from
    ENROLLMENT_STATUS a,
    PREFECTURE b
  where
    a.PF_CODE = b.PF_CODE and
    a.SURVEY_YEAR = 2020 and
    a.GRADUATE is not null
  group by
    SV_YEAR, PREFECTURE, KIND
)
order by
  PREFECTURE asc,
  NUM asc
提出情報
提出日時2022/09/21 21:02:19
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者ks2m
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量99 MB
メッセージ
テストケース(通過数/総数)
1/2
状態
メモリ使用量
データパターン1
WA
98 MB
データパターン2
AC
99 MB