SELECT 
 FOR.PF_CODE AS 都道府県コード
 , PER.PF_NAME AS 都道府県名
 , MAX(CASE FOR.CNT WHEN 1 THEN NAT.NATION_NAME ELSE NULL END) AS "1位 国名"
 , MAX(CASE FOR.CNT WHEN 1 THEN FOR.AMT ELSE 0 END) AS "1位 人数"
 , MAX(CASE FOR.CNT WHEN 2 THEN NAT.NATION_NAME ELSE NULL END) AS "2位 国名"
 , MAX(CASE FOR.CNT WHEN 2 THEN FOR.AMT ELSE 0 END) AS "2位 人数"
 , MAX(CASE FOR.CNT WHEN 3 THEN NAT.NATION_NAME ELSE NULL END) AS "3位 国名"
 , MAX(CASE FOR.CNT WHEN 3 THEN FOR.AMT ELSE 0 END) AS "3位 人数"
 , SUM(FOR.AMT) AS 合計人数
FROM PREFECTURE PER
INNER JOIN
(SELECT PF_CODE, NATION_CODE, AMT, RANK() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC) AS CNT FROM FOREIGNER WHERE NATION_CODE != '113') FOR
ON PER.PF_CODE = FOR.PF_CODE
AND FOR.CNT <= 3
INNER JOIN NATIONALITY NAT
ON FOR.NATION_CODE = NAT.NATION_CODE
GROUP BY PER.PF_CODE, PER.PF_NAME
ORDER BY 合計人数 DESC, 都道府県コード ASC