SELECT
a.PF_CODE as 都道府県コード,
b.PF_NAME as 都道府県名,
past.TOTAL_AMT as 総人口2015年,
present.TOTAL_AMT as 総人口2020年,
ROUND(
CAST((present.TOTAL_AMT/past.TOTAL_AMT) as REAL)*100,0) as 人口増加率
FROM
POPU_TRANSITION a
JOIN
PREFECTURE b
ON
a.PF_CODE = b.PF_CODE
JOIN
(SELECT
PF_CODE,
TOTAL_AMT
FROM
POPU_TRANSITION
WHERE SURVEY_YEAR = 2015) as past
ON
a.PF_CODE = past.PF_CODE
JOIN
(SELECT
PF_CODE,
TOTAL_AMT
FROM
POPU_TRANSITION
WHERE SURVEY_YEAR = 2020) as present
ON
a.PF_CODE = present.PF_CODE
ORDER BY 人口増加率 DESC,
a.PF_CODE asc