with tmp as (
    select *, 
           rank() over(partition by a.PF_CODE order by AMT desc, a.NATION_CODE) as rank
    from FOREIGNER a
    inner join NATIONALITY b on a.NATION_CODE = b.NATION_CODE
    inner join PREFECTURE  c on a.PF_CODE  = c.PF_CODE
    where b.NATION_CODE != '113'
)
select PF_CODE as 都道府県コード,
       PF_NAME as 都道府県名,
       max(case when rank = 1 then NATION_NAME else null end) as '1位 国名',
       sum(case when rank = 1 then AMT else 0 end)            as '1位 人数',
       max(case when rank = 2 then NATION_NAME else null end) as '2位 国名',
       sum(case when rank = 2 then AMT else 0 end)            as '2位 人数',
       max(case when rank = 3 then NATION_NAME else null end) as '3位 国名',
       sum(case when rank = 3 then AMT else 0 end)            as '3位 人数',
       sum(AMT) as 合計人数
from tmp
group by 1,2
order by 合計人数 desc, 都道府県コード