WITH data AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS n
    FROM FOREIGNER AS f
    JOIN NATIONALITY AS n
    USING (NATION_CODE)
    JOIN PREFECTURE AS p
    USING (PF_CODE)
    WHERE NATION_CODE != "113"
), total AS (
    SELECT
        PF_CODE,
        SUM(AMT) AS t
    FROM data
    GROUP BY PF_CODE
)


SELECT
    c1.PF_CODE AS 都道府県コード,
    c1.PF_NAME AS 都道府県名,
    c1.NATION_NAME AS `1位 国名`,
    c1.AMT AS `1位 人数`,
    c2.NATION_NAME AS `2位 国名`,
    c2.AMT AS `2位 人数`,
    c3.NATION_NAME AS `3位 国名`,
    c3.AMT AS `3位 人数`,
    t AS 合計人数
FROM total
LEFT OUTER JOIN data AS c1
ON total.PF_CODE = c1.PF_CODE AND c1.n = 1
LEFT OUTER JOIN data AS c2
ON c1.PF_CODE = c2.PF_CODE AND c2.n = 2
LEFT OUTER JOIN data AS c3
ON c1.PF_CODE = c3.PF_CODE AND c3.n = 3
ORDER BY 合計人数 DESC, 都道府県コード