select
pf_code as 都道府県コード,
pf_name as 都道府県名,
max(case seq
    when 1 then nation_name else null end) as '1位 国名',
max(case seq
    when 1 then amt else null end) as '1位 人数',
max(case seq
    when 2 then nation_name else null end) as '2位 国名',
max(case seq
    when 2 then amt else null end) as '2位 人数',
max(case seq
    when 3 then nation_name else null end) as '3位 国名',
max(case seq
    when 3 then amt else null end) as '3位 人数',
sum(amt) as '合計人数'
from(
select
 p.pf_code,
 p.pf_name,
 n.nation_name,
 f.amt,
 rank() over(partition by p.pf_code order by f.amt desc,n.nation_code) as seq
from
FOREIGNER f,
NATIONALITY n,
PREFECTURE p
where p.pf_code = f.pf_code
and f.nation_code = n.nation_code
and f.nation_code != '113'
)
group by
pf_code,
pf_name
order by sum(amt) desc