WITH RankedForeigners AS (
    SELECT 
        FOREIGNER.PF_CODE,
        PREFECTURE.PF_NAME,
        NATIONALITY.NATION_NAME,
        FOREIGNER.AMT,
        ROW_NUMBER() OVER (
            PARTITION BY FOREIGNER.PF_CODE 
            ORDER BY FOREIGNER.AMT DESC
        ) AS RANK
    FROM FOREIGNER
    LEFT JOIN PREFECTURE ON FOREIGNER.PF_CODE = PREFECTURE.PF_CODE
    LEFT JOIN NATIONALITY ON FOREIGNER.NATION_CODE=NATIONALITY.NATION_CODE
    WHERE NOT FOREIGNER.NATION_CODE = 113
)
SELECT 
    PF_CODE AS 都道府県コード,
    PF_NAME AS 都道府県名,
    MAX(CASE WHEN RANK = 1 THEN NATION_NAME END) AS "1位 国名",
    MAX(CASE WHEN RANK = 1 THEN AMT END) AS "1位 人数",
    MAX(CASE WHEN RANK = 2 THEN NATION_NAME END) AS "2位 国名",
    MAX(CASE WHEN RANK = 2 THEN AMT END) AS "2位 人数",
    MAX(CASE WHEN RANK = 3 THEN NATION_NAME END) AS "3位 国名",
    MAX(CASE WHEN RANK = 3 THEN AMT END) AS "3位 人数",
    MAX(CASE WHEN RANK = 1 THEN AMT ELSE 0 END) +
    MAX(CASE WHEN RANK = 2 THEN AMT ELSE 0 END) +
    MAX(CASE WHEN RANK = 3 THEN AMT ELSE 0 END) AS "合計人数"
FROM RankedForeigners
GROUP BY PF_CODE, PF_NAME
ORDER BY 合計人数 DESC,都道府県コード ASC