SELECT
    p.PF_CODE AS 都道府県コード
    , p.PF_NAME AS 都道府県名
    , MAX(CASE fn.RANK WHEN 1 THEN NATION_NAME ELSE NULL END) AS "1位 国名"
    , MAX(CASE fn.RANK WHEN 1 THEN AMT ELSE 0 END) AS "1位 人数"
    , MAX(CASE fn.RANK WHEN 2 THEN NATION_NAME ELSE NULL END) AS "2位 国名"
    , MAX(CASE fn.RANK WHEN 2 THEN AMT ELSE 0 END) AS "2位 人数"
    , MAX(CASE fn.RANK WHEN 3 THEN NATION_NAME ELSE NULL END) AS "3位 国名"
    , MAX(CASE fn.RANK WHEN 3 THEN AMT ELSE 0 END) AS "3位 人数"
    , SUM(fn.AMT) AS 合計人数
FROM
    PREFECTURE p
JOIN
    (
        SELECT
            f.PF_CODE
            ,f.NATION_CODE
            ,n.NATION_NAME
            ,f.AMT
            ,RANK() OVER (PARTITION BY f.PF_CODE ORDER BY f.AMT desc, PF_CODE desc) as rank
        FROM
            FOREIGNER f
        JOIN
            NATIONALITY n
        ON
            f.NATION_CODE = n.NATION_CODE
        where
            f.NATION_CODE <> '113'
    ) fn
ON
    p.PF_CODE = fn.PF_CODE
GROUP BY
    p.PF_CODE
ORDER BY
    合計人数 desc , 都道府県コード desc