with
    framt   as  (
    select
        fr.pf_code  as  pf_code
    ,   fr.nation_code  as  nation_code
    ,   n.nation_name   as  nation_name
    ,   sum(fr.amt) as  total_amt
    ,   row_number() over (partition by fr.pf_code order by sum(fr.amt) desc) as rank
    from    
        foreigner   fr
    inner join
        nationality n
    on  fr.nation_code  =   n.nation_code
    where
        fr.nation_code  != '113'
    group by
        fr.pf_code
    ,   fr.nation_code
)
select
    pfrank.pf_code  as  '都道府県コード'
,   pfrank.pf_name  as  '都道府県名'
,   fr1.nation_name  as  '1位 国名'
,   fr1.total_amt  as  '1位 人数'
,   fr2.nation_name  as  '2位 国名'
,   fr2.total_amt  as  '2位 人数'
,   fr3.nation_name  as  '3位 国名'
,   fr3.total_amt  as  '3位 人数'
,   sum(fr.amt) as  '合計人数'
from
    (select
        pf.pf_code as  pf_code
    ,   pf.pf_name as  pf_name
    ,   1   as first
    ,   2   as second
    ,   3   as third
    from
        prefecture pf
) pfrank
inner join
    framt   fr1
on  
    pfrank.pf_code  =   fr1.pf_code
and pfrank.first  =   fr1.rank
inner join
    framt   fr2
on  
    pfrank.pf_code  =   fr2.pf_code
and pfrank.second  =   fr2.rank
inner join
    framt   fr3
on  
    pfrank.pf_code  =   fr3.pf_code
and pfrank.third  =   fr3.rank
inner join
    foreigner   fr
on  pfrank.pf_code  =   fr.pf_code
and fr.nation_code != '113'
group by pfrank.pf_code
order by
    sum(fr.amt) desc
,   pfrank.pf_code  desc
;