ソースコード
with elem as (
select PF_CODE, "小学校" as KIND, sum(ELEMENTARY) as total, 1 as ord from ENROLLMENT_STATUS where SURVEY_YEAR = 2020
group by PF_CODE having sum(ELEMENTARY) is not null
),
middle as(
select PF_CODE,  "中学校" as KIND, sum(MIDDLE) as total, 2 as ord from ENROLLMENT_STATUS where SURVEY_YEAR = 2020
group by PF_CODE having sum(MIDDLE) is not null
),
high as (
select PF_CODE,  "高校" as KIND, sum(HIGH) as total, 3 as ord from ENROLLMENT_STATUS where SURVEY_YEAR = 2020
group by PF_CODE having sum(HIGH) is not null
),
low_high as (
select PF_CODE,  "短大" as KIND, sum(JUNIOR_CLG) as total, 4 as ord from ENROLLMENT_STATUS where SURVEY_YEAR = 2020
group by PF_CODE having sum(JUNIOR_CLG) is not null
),
coll as (
select PF_CODE,  "大学" as KIND, sum(COLLEGE) as total, 5 as ord from ENROLLMENT_STATUS where SURVEY_YEAR = 2020
group by PF_CODE having sum(COLLEGE) is not null
),
grad as (
select PF_CODE,  "大学院" as KIND, sum(GRADUATE) as total, 6 as ord from ENROLLMENT_STATUS where SURVEY_YEAR = 2020
group by PF_CODE having sum(GRADUATE) is not null
),
summary as (
select
2020 as SV_YEAR,
PREFECTURE.PF_NAME as PREFECTURE,
"小学校" as KIND,
elem.total as AMT,
ord
from
PREFECTURE
join elem on elem.PF_CODE = PREFECTURE.PF_CODE
UNION
select
2020 as SV_YEAR,
PREFECTURE.PF_NAME as PREFECTURE,
"中学校" as KIND,
middle.total as AMT,
ord
from
PREFECTURE
join middle on middle.PF_CODE = PREFECTURE.PF_CODE
UNION
select
2020 as SV_YEAR,
PREFECTURE.PF_NAME as PREFECTURE,
"高校" as KIND,
high.total as AMT,
ord
from
PREFECTURE
join high on high.PF_CODE = PREFECTURE.PF_CODE
UNION
select
2020 as SV_YEAR,
PREFECTURE.PF_NAME as PREFECTURE,
"短大" as KIND,
low_high.total as AMT,
ord
from
PREFECTURE
join low_high on low_high.PF_CODE = PREFECTURE.PF_CODE
UNION
select
2020 as SV_YEAR,
PREFECTURE.PF_NAME as PREFECTURE,
"大学" as KIND,
coll.total as AMT,
ord
from
PREFECTURE
join coll on coll.PF_CODE = PREFECTURE.PF_CODE
UNION
select
2020 as SV_YEAR,
PREFECTURE.PF_NAME as PREFECTURE,
"大学院" as KIND,
grad.total as AMT,
ord
from
PREFECTURE
join grad on grad.PF_CODE = PREFECTURE.PF_CODE
)
select SV_YEAR, PREFECTURE, KIND, AMT from summary
order by PREFECTURE , ord
提出情報
提出日時2022/09/21 22:10:14
コンテスト第2回 SQLコンテスト
問題就学状況の表示変換
受験者nenecchi
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量102 MB
メッセージ
テストケース(通過数/総数)
1/2
状態
メモリ使用量
データパターン1
WA
94 MB
データパターン2
AC
102 MB