WITH NATIO_AMT AS (
    SELECT
        PF_CODE
        , SUM(CASE WHEN 順位 = 1 THEN NATION_CODE ELSE 0 END) NATIO_1
        , SUM(CASE WHEN 順位 = 1 THEN AMT ELSE 0 END)  AMT_1
        , SUM(CASE WHEN 順位 = 2 THEN NATION_CODE ELSE 0 END) NATIO_2
        , SUM(CASE WHEN 順位 = 2 THEN AMT ELSE 0 END)  AMT_2
        , SUM(CASE WHEN 順位 = 3 THEN NATION_CODE ELSE 0 END) NATIO_3
        , SUM(CASE WHEN 順位 = 3 THEN AMT ELSE 0 END)  AMT_3
        , SUM(AMT) AMT_TTL
    FROM (
        SELECT
            PF_CODE
            , ROW_NUMBER() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE ASC) 順位
            , NATION_CODE
            , AMT
        FROM
            FOREIGNER
        WHERE
            NATION_CODE <> 113
        ) a
    GROUP BY
        PF_CODE
)
SELECT 
    a.PF_CODE        都道府県コード
    , b.PF_NAME      都道府県名
    , n1.NATION_NAME '1位 国名'
    , a.AMT_1        '1位 人数'
    , n2.NATION_NAME '2位 国名'
    , a.AMT_2        '2位 人数'
    , n3.NATION_NAME '3位 国名'
    , a.AMT_3        '3位 人数'
    , a.AMT_TTL      合計人数
FROM NATIO_AMT a
    INNER JOIN PREFECTURE b
        ON a.PF_CODE = b.PF_CODE
    INNER JOIN NATIONALITY n1
        ON a.NATIO_1 = n1.NATION_CODE
    INNER JOIN NATIONALITY n2
        ON a.NATIO_2 = n2.NATION_CODE
    INNER JOIN NATIONALITY n3
        ON a.NATIO_3 = n3.NATION_CODE
ORDER BY
    合計人数 DESC
    , a.PF_CODE
;