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