SELECT
    PF_CODE AS 都道府県コード,
    PF_NAME AS 都道府県名,
    MAX(CASE F.RANKING WHEN 1 THEN NA.NATION_NAME ELSE NULL END) AS "1位 国名",
    MAX(CASE F.RANKING WHEN 1 THEN F.AMT ELSE 0 END) AS "1位 人数",
    MAX(CASE F.RANKING WHEN 2 THEN NA.NATION_NAME ELSE NULL END) AS "2位 国名",
    MAX(CASE F.RANKING WHEN 2 THEN F.AMT ELSE 0 END) AS "2位 人数",
    MAX(CASE F.RANKING WHEN 3 THEN NA.NATION_NAME ELSE NULL END) AS "3位 国名",
    MAX(CASE F.RANKING WHEN 3 THEN F.AMT ELSE 0 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 RANKING
            
        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