select
    sub.PF_CODE as 都道府県コード
    , sub.PF_NAME as 都道府県名
    , MAX(CASE SUB.ROW_NUMBER_OVER WHEN 1 THEN NATION_NAME ELSE NULL END) AS "1位 国名"
    , MAX(CASE SUB.ROW_NUMBER_OVER WHEN 1 THEN AMT ELSE 0 END) AS "1位 人数"
    , MAX(CASE SUB.ROW_NUMBER_OVER WHEN 2 THEN NATION_NAME ELSE NULL END) AS "2位 国名"
    , MAX(CASE SUB.ROW_NUMBER_OVER WHEN 2 THEN AMT ELSE 0 END) AS "2位 人数"
    , MAX(CASE SUB.ROW_NUMBER_OVER WHEN 3 THEN NATION_NAME ELSE NULL END) AS "3位 国名"
    , MAX(CASE SUB.ROW_NUMBER_OVER WHEN 3 THEN AMT ELSE 0 END) AS "3位 人数"
    , SUM(sub.AMT) as 合計人数
from
    ( 
        select
            *
            , ROW_NUMBER() OVER (PARTITION BY fr.PF_CODE ORDER BY AMT DESC) AS ROW_NUMBER_OVER 
        from
            FOREIGNER as fr 
            inner join PREFECTURE as pf 
                on fr.PF_CODE = pf.PF_CODE 
            inner join NATIONALITY as na 
                on fr.NATION_CODE = na.NATION_CODE 
        WHERE
            NOT fr.NATION_CODE = "113" 
        group by
            fr.PF_CODE
            , fr.NATION_CODE 
        order by
            fr.PF_CODE
    ) as sub 
group by
    PF_CODE
order by 合計人数 desc, 都道府県コード asc