ソースコード
select
     main.SURVEY_YEAR as SV_YEAR
    ,pre.PF_NAME as PREFECTURE
    ,case when p.pattern = 1 then '小学校'
          when p.pattern = 2 then '中学校'
          when p.pattern = 3 then '高校'
          when p.pattern = 4 then '短大'
          when p.pattern = 5 then '大学'
          when p.pattern = 6 then '大学院'
          else null
     end as KIND
    ,sum(case when p.pattern = 1 then ELEMENTARY
          when p.pattern = 2 then MIDDLE
          when p.pattern = 3 then HIGH
          when p.pattern = 4 then JUNIOR_CLG
          when p.pattern = 5 then COLLEGE
          when p.pattern = 6 then GRADUATE
          else null
     end) as AMT
from
    ENROLLMENT_STATUS as main
inner join
    PREFECTURE as pre using(PF_CODE)
cross join
(
   SELECT 1 AS pattern
    UNION ALL SELECT 2 AS pattern
    UNION ALL SELECT 3 AS pattern
    UNION ALL SELECT 4 AS pattern
    UNION ALL SELECT 5 AS pattern
    UNION ALL SELECT 6 AS pattern
) as p
where
    main.SURVEY_YEAR = 2020
group by
     SV_YEAR
    ,PREFECTURE
    ,KIND
having
    AMT is not null
order by
     PF_CODE asc
    ,p.pattern asc
提出情報
提出日時2022/09/21 18:18:36
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者kon-kitsune
状態 (詳細)AC
(Accepted: 正答)
メモリ使用量97 MB
メッセージ
テストケース(通過数/総数)
2/2
状態
メモリ使用量
データパターン1
AC
97 MB
データパターン2
AC
95 MB