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