select
都道府県コード,
PF_NAME as 都道府県名,
総人口2015年,
総人口2020年,
人口増加率
from
(
select
y2015.PF_CODE as 都道府県コード,
y2015.TOTAL_AMT as 総人口2015年,
y2020.TOTAL_AMT as 総人口2020年,
round(cast(100*y2020.TOTAL_AMT as real)/cast(y2015.TOTAL_AMT as real)) as 人口増加率
from
(
select
PF_CODE,
TOTAL_AMT
from
POPU_TRANSITION
where
SURVEY_YEAR
=
2015
) as y2015
inner join
(
select
PF_CODE,
TOTAL_AMT
from
POPU_TRANSITION
where
SURVEY_YEAR
=
2020
) as y2020
on
y2015.PF_CODE
=
y2020.PF_CODE
) as abc
inner join
PREFECTURE
on
abc.都道府県コード
=
PREFECTURE.PF_CODE
where
人口増加率
>
100
order by
人口増加率
desc,
都道府県コード
asc
;