SELECT 
FR1.PF_CODE AS 都道府県コード,
PF_NAME AS 都道府県名,
FR1.NATION_NAME AS `1位 国名`,
FR1.AMT AS `1位 人数`,
FR2.NATION_NAME AS `2位 国名`,
FR2.AMT AS `2位 人数`,
FR3.NATION_NAME AS `3位 国名`,
FR3.AMT AS `3位 人数`,
TOTAL AS 合計人数
FROM
PREFECTURE AS PF 

JOIN

(SELECT * FROM
(SELECT 
RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC) AS RANK_ID,
PF_CODE,
NATION_NAME,
AMT
FROM
FOREIGNER  AS FR
JOIN
NATIONALITY AS NT 
ON FR.NATION_CODE = NT.NATION_CODE)
WHERE RANK_ID=1) AS FR1
ON PF.PF_CODE = FR1.PF_CODE

JOIN

(SELECT * FROM
(SELECT 
RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC) AS RANK_ID,
PF_CODE,
NATION_NAME,
AMT
FROM
FOREIGNER  AS FR
JOIN
NATIONALITY AS NT 
ON FR.NATION_CODE = NT.NATION_CODE)
WHERE RANK_ID=2)AS FR2 
ON FR1.PF_CODE = FR2.PF_CODE

JOIN

(SELECT * FROM
(SELECT 
RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC) AS RANK_ID,
PF_CODE,
NATION_NAME,
AMT
FROM
FOREIGNER  AS FR
JOIN
NATIONALITY AS NT 
ON FR.NATION_CODE = NT.NATION_CODE)
WHERE RANK_ID=3) AS FR3
ON FR1.PF_CODE = FR3.PF_CODE

JOIN

(SELECT SUM(AMT) AS TOTAL, PF_CODE
FROM FOREIGNER
GROUP BY PF_CODE
) AS FR
ON PF.PF_CODE = FR.PF_CODE

ORDER BY 合計人数 DESC, 都道府県コード ASC