with tmp as (
select
    f.pf_code
    ,f.nation_code
    ,n.nation_name
    ,f.amt
    ,rank() over(partition by f.pf_code order by f.amt desc , f.nation_code asc) as rn
from
    foreigner f
inner join NATIONALITY as n
    on f.nation_code= n.nation_code
where
    n.nation_code != '113'
), s as(
select
    pf_code
    ,sum(amt) as amt
from
    tmp
group by
    pf_code
)
select
    r1.pf_code as '都道府県コード'
    ,p.pf_name as '都道府県名'
    ,r1.nation_name as '1位 国名'
    ,r1.amt as '1位 人数'
    ,r2.nation_name as '2位 国名'
    ,r2.amt as '2位 人数'
    ,r3.nation_name as '3位 国名'
    ,r3.amt as '3位 人数'
    ,s.amt as '合計人数'
from
    tmp as r1
cross join tmp as r2
cross join tmp as r3
inner join s
    on r1.pf_code = s.pf_code
inner join prefecture as p
    on r1.pf_code = p.pf_code
where
    r1.pf_code = r2.pf_code
    and r1.pf_code = r3.pf_code
    and r1.rn = 1
    and r2.rn = 2
    and r3.rn = 3
order by
    合計人数 desc
    ,都道府県コード asc