with t as (
    select f.pf_code, pf_name, nation_name, amt, rank() over(partition by f.pf_code order by amt desc, f.nation_code) as r
    from FOREIGNER as f
    inner join NATIONALITY as n on n.NATION_CODE=f.NATION_CODE
    inner join PREFECTURE as p on p.PF_CODE=f.PF_CODE
    where f.NATION_CODE!=113
)
select pf_code as 都道府県コード,
pf_name as 都道府県名,
max(case when r=1 then nation_name end) as "1位 国名",
max(case when r=1 then amt end) as "1位 人数",
max(case when r=2 then nation_name end) as "2位 国名",
max(case when r=2 then amt end) as "2位 人数",
max(case when r=3 then nation_name end) as "3位 国名",
max(case when r=3 then amt end) as "3位 人数",
(select sum(amt) from foreigner as f where f.PF_CODE=t.PF_CODE) as "合計人数"
from t
group by pf_code
order by 9 desc,1