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