with tmp as (
    select 
        pf_code,
        f.nation_code,
        nation_name,
        rank() over(partition by pf_code order by amt desc,f.nation_code) as rankno,
        amt 
    from foreigner as f 
    inner join nationality as n 
    on f.nation_code=n.nation_code 
    where f.nation_code not in ('113') 
),tmp1 as (
    select
        pf_code,
        max(case when rankno=1 then nation_name end) as '1位 国名',
        max(case when rankno=1 then amt end) as '1位 人数',
        max(case when rankno=2 then nation_name end) as '2位 国名',
        max(case when rankno=2 then amt end) as '2位 人数',
        max(case when rankno=3 then nation_name end) as '3位 国名',
        max(case when rankno=3 then amt end) as '3位 人数',
        sum(amt) as 合計人数
    from tmp
    group by pf_code
)
select 
    tmp1.pf_code as 都道府県コード,
    pf_name as 都道府県名,
    tmp1.'1位 国名' ,
    tmp1.'1位 人数',
    tmp1.'2位 国名',
    tmp1.'2位 人数',
    tmp1.'3位 国名',
    tmp1.'3位 人数',
    tmp1.合計人数 
from tmp1 
inner join prefecture as p 
on p.pf_code=tmp1.pf_code 
order by 合計人数 desc,都道府県コード;