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