SELECT
    FR.PF_CODE AS '都道府県コード',
    PF_NAME AS '都道府県名',
    FR.NATION_NAME AS '1位 国名',
    FR.AMT AS '1位 人数',
    FR2.NATION_NAME AS '2位 国名',
    FR2.AMT AS '2位 人数',
    FR3.NATION_NAME AS '3位 国名',
    FR3.AMT AS '3位 人数',
    FR_SUM.SUM AS '合計人数'
FROM
    (SELECT
        PF_CODE,
        NATION_NAME,
        AMT
    FROM
        (
            SELECT 
                *, 
                RANK()
                    OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE DESC) AS RANK
            FROM
                FOREIGNER
            WHERE
                NATION_CODE != '113'
        ) AS FR
        JOIN
            NATIONALITY AS NT
        ON
            NT.NATION_CODE = FR.NATION_CODE
    WHERE FR.RANK = 1) AS FR
JOIN
    (SELECT
        PF_CODE,
        NATION_NAME,
        AMT
    FROM
        (
            SELECT *, RANK()
                OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE DESC) AS RANK
            FROM
                FOREIGNER
            WHERE
                NATION_CODE != '113'
        ) AS FR
        JOIN
            NATIONALITY AS NT
        ON
            NT.NATION_CODE = FR.NATION_CODE
    WHERE FR.RANK = 2) AS FR2
ON
    FR2.PF_CODE = FR.PF_CODE
JOIN
    (SELECT
        PF_CODE,
        NATION_NAME,
        AMT
    FROM
        (
            SELECT *, RANK()
                OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE DESC) AS RANK
            FROM
                FOREIGNER
            WHERE
                NATION_CODE != '113'
        ) AS FR
        JOIN
            NATIONALITY AS NT
        ON
            NT.NATION_CODE = FR.NATION_CODE
    WHERE FR.RANK = 3) AS FR3
ON
    FR3.PF_CODE = FR.PF_CODE
JOIN
    (SELECT PF_CODE, SUM(AMT) AS SUM
    FROM FOREIGNER
    WHERE NATION_CODE != '113'
    GROUP BY PF_CODE) AS FR_SUM
ON
    FR_SUM.PF_CODE = FR.PF_CODE
JOIN
    PREFECTURE AS PF
ON
    PF.PF_CODE = FR.PF_CODE
ORDER BY
    SUM DESC,
    FR.PF_CODE ASC