select
 p.PF_CODE as 都道府県コード
,p.PF_NAME as 都道府県名
,max(case when f.AMT_RANK = 1 then n.NATION_NAME else '' end) as "1位 国名"
,sum(case when f.AMT_RANK = 1 then f.AMT else 0 end) as "1位 人数"
,max(case when f.AMT_RANK = 2 then n.NATION_NAME else '' end) as "2位 国名"
,sum(case when f.AMT_RANK = 2 then f.AMT else 0 end) as "2位 人数"
,max(case when f.AMT_RANK = 3 then n.NATION_NAME else '' end) as "3位 国名"
,sum(case when f.AMT_RANK = 3 then f.AMT else 0 end) as "3位 人数"
,sum(f.AMT) as 合計人数
from PREFECTURE p
join (
select PF_CODE, NATION_CODE, AMT, RANK() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AMT_RANK
from (
select PF_CODE, NATION_CODE, SUM(AMT) AMT
from FOREIGNER
where NATION_CODE <> '113'
group by PF_CODE, NATION_CODE ) F
) f on p.PF_CODE = f.PF_CODE
join NATIONALITY n on f.NATION_CODE = n.NATION_CODE
group by p.PF_CODE, p.PF_NAME
order by sum(f.AMT) desc, p.PF_CODE