WITH
    ranked_foreigner AS (
        SELECT
            -- primary
            A.PF_CODE,
            -- primary
            A.NATION_CODE,
            B.NATION_NAME,
            A.AMT,
            RANK() OVER (
                PARTITION BY
                    A.PF_CODE
                ORDER BY
                    A.AMT DESC
            ) AS AMT_RANK
        FROM FOREIGNER AS A
            INNER JOIN NATIONALITY AS B
                ON A.NATION_CODE = B.NATION_CODE
        WHERE
            A.NATION_CODE <> 113
    ),
    -- どうやらその他を除いた合計で良いらしい
    -- であれば、 ranked_foreigner で設定すれば良いが今回は治すの面倒なのでこっちで.
    -- その他を含めた合計は `GROUP BY` の句を取り除いたもの
    pf_total_amt_table AS (
        SELECT
            -- primary
            A.PF_CODE,
            SUM(A.AMT) AS total_amt
        FROM  FOREIGNER AS A
        WHERE
            A.NATION_CODE <> 113
        GROUP BY
            A.PF_CODE
    ),
    table1 AS (
        SELECT
            -- primary
            PF_CODE,
            NATION_NAME,
            AMT
        FROM ranked_foreigner
        WHERE
            ranked_foreigner.AMT_RANK = 1
    ),
    table2 AS (
        SELECT
            -- primary
            PF_CODE,
            NATION_NAME,
            AMT
        FROM
            ranked_foreigner
        WHERE
            ranked_foreigner.AMT_RANK = 2
    ),
    table3 AS (
        SELECT
            -- primary
            PF_CODE,
            NATION_NAME,
            AMT
        FROM
            ranked_foreigner
        WHERE
            ranked_foreigner.AMT_RANK = 3
    )
SELECT
    A.PF_CODE as `都道府県コード`,
    A.PF_NAME as `都道府県名`,
    table1.NATION_NAME as `1位 国名`,
    table1.AMT         as `1位 人数`,
    table2.NATION_NAME as `2位 国名`,
    table2.AMT         as `2位 人数`,
    table3.NATION_NAME as `3位 国名`,
    table3.AMT         as `3位 人数`,
    pf_total_amt_table.total_amt AS `合計人数`
FROM PREFECTURE AS A
    INNER JOIN table1
        ON A.PF_CODE = table1.PF_CODE
    INNER JOIN table2
        ON A.PF_CODE = table2.PF_CODE
    INNER JOIN table3
        ON A.PF_CODE = table3.PF_CODE
    INNER JOIN pf_total_amt_table
        ON A.PF_CODE = pf_total_amt_table.PF_CODE
ORDER BY
    `合計人数` DESc,
    A.PF_CODE ASC
;