with tmp as (
    select 
        pf_code,
        f.nation_code,
        nation_name,
        rank() over(partition by pf_code order by amt desc) 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,
        nation_name as '1位 国名' ,
        amt as '1位 人数' 
    from tmp 
    where rankno=1 
), tmp2 as (
    select 
        tmp1.pf_code,
        tmp1.'1位 国名',
        tmp1.'1位 人数',
        nation_name as '2位 国名' ,
        amt as '2位 人数' 
    from tmp 
    inner join tmp1 
    on tmp1.pf_code=tmp.pf_code 
    where rankno=2
), tmp3 as (
    select 
        tmp2.pf_code,
        tmp2.'1位 国名',
        tmp2.'1位 人数',
        tmp2.'2位 国名',
        tmp2.'2位 人数',
        nation_name as '3位 国名' ,
        amt as '3位 人数' 
    from tmp 
    inner join tmp2 
    on tmp2.pf_code=tmp.pf_code 
    where rankno=3
) ,tmp4 as (
    select 
        tmp3.pf_code,
        tmp3.'1位 国名',
        tmp3.'1位 人数',
        tmp3.'2位 国名',
        tmp3.'2位 人数',
        tmp3.'3位 国名',
        tmp3.'3位 人数',
        sum(amt) as 合計人数 
    from foreigner as f 
    inner join tmp3 
    on tmp3.pf_code=f.pf_code
    where nation_code not in ('113') 
    group by f.pf_code 
)
select 
    tmp4.pf_code as 都道府県コード, 
    pf_name as 都道府県名,
    tmp4.'1位 国名' ,
    tmp4.'1位 人数',
    tmp4.'2位 国名',
    tmp4.'2位 人数',
    tmp4.'3位 国名',
    tmp4.'3位 人数',
    tmp4.合計人数
from tmp4 
inner join prefecture as p 
on p.pf_code=tmp4.pf_code 
order by 合計人数 desc,都道府県コード;