SELECT
    PF_CODE AS 都道府県コード,
    PF_NAME AS 都道府県名,
    MAX(CASE WHEN F.RANKING = 1 THEN NATION_NAME END) AS "1位 国名",
    MAX(CASE WHEN F.RANKING = 1 THEN AMT END) AS "1位 人数",
    MAX(CASE WHEN F.RANKING = 2 THEN NATION_NAME END) AS "2位 国名",
    MAX(CASE WHEN F.RANKING = 2 THEN AMT END) AS "2位 人数",
    MAX(CASE WHEN F.RANKING = 3 THEN NATION_NAME END) AS "3位 国名",
    MAX(CASE WHEN F.RANKING = 3 THEN AMT END) AS "3位 人数",
    SUM(F.AMT) AS "合計人数"
FROM
    (
        SELECT
            PF_CODE
            NATION_CODE
            AMT
            RANK() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS RANK
        FROM
            FOREIGNER
    
        WHERE
            NATION_CODE != "113"
    ) AS F
    
    INNER JOIN PREFECTURE AS PR USING(PF_CODE)
    INNER JOIN NATIONALITY AS NA USING(NATION_CODE)
GROUP BY
    F.PF_CODE
ORDER BY
    合計人数　DESC,
    PF_CODE ASC