select
    PF_CODE AS 都道府県コード
    , PF_NAME AS 都道府県名
    , MAX(CASE SUB.RANK WHEN 1 THEN NATION_NAME ELSE NULL END) AS "1位 国名"
    , MAX(CASE SUB.RANK WHEN 1 THEN AMT ELSE 0 END) AS "1位 人数"
    , MAX(CASE SUB.RANK WHEN 2 THEN NATION_NAME ELSE NULL END) AS "2位 国名"
    , MAX(CASE SUB.RANK WHEN 2 THEN AMT ELSE 0 END) AS "2位 人数"
    , MAX(CASE SUB.RANK WHEN 3 THEN NATION_NAME ELSE NULL END) AS "3位 国名"
    , MAX(CASE SUB.RANK WHEN 3 THEN AMT ELSE 0 END) AS "3位 人数"
    , SUM(AMT) AS 合計人数 
from(
    select 
    f.pf_code as pf_code
    ,p.pf_name as pf_name
    ,n.nation_name as nation_name
    ,rank()over(partition by f.pf_code order by amt desc,f.nation_code asc) as rank
    ,amt
    from foreigner as f
    inner join PREFECTURE as p
    on p.pf_code = f.pf_code
    inner join nationality as n
    on n.nation_code = f.nation_code
    where f.nation_code <>'113'
) as SUB
group by pf_code,pf_name
order by 合計人数 desc ,pf_code asc