With R as (
select
    P.PF_CODE
    ,P.PF_NAME
    ,N.NATION_CODE
    ,N.NATION_NAME
    ,AMT
    ,rank() over (partition by P.PF_CODE order by AMT desc) as RANK
    ,sum(AMT) over (partition by P.PF_CODE) as SUM_AMT
from
    (select * from FOREIGNER where NATION_CODE<>'113') F
    inner join
        PREFECTURE P
        on F.PF_CODE=P.PF_CODE
    inner join
        NATIONALITY N 
        on N.NATION_CODE=F.NATION_CODE
)

select
    R1.PF_CODE as '都道府県コード'
    ,R1.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位 人数'
    ,R1.SUM_AMT as '合計人数'
from
    (select * from R where RANK=1) R1
    left outer join
        (select * from R where RANK=2) R2
        on R1.PF_CODE=R2.PF_CODE
    left outer join
        (select * from R where RANK=3) R3
        on R1.PF_CODE=R3.PF_CODE
order by R1.SUM_AMT desc, R1.PF_CODE
;