with master as (
select * from (
select *, row_number() over(partition by pf_code order by amt desc, nation_code) as ranking
from foreigner
where nation_code <> '113')
where ranking <=3
), total as (
select pf_code, sum(amt) as ttl_amt
from foreigner
where nation_code <> '113'
group by pf_code)

select pf_code as 都道府県コード, pf_name as 都道府県名
,mna1 as '1位 国名', mam1 as '1位 人数'
,mna2 as '2位 国名', mam2 as '2位 人数'
,mna3 as '3位 国名', mam3 as '3位 人数'
,ttl_amt as 合計人数
from(
select pf_code, pf_name
,max(na1) as mna1
,max(am1) as mam1
,max(na2) as mna2
,max(am2) as mam2
,max(na3) as mna3
,max(am3) as mam3
from (
select pf_code, pf_name
,case ranking when 1 then nation_name end as na1
,case ranking when 1 then amt end as am1
,case ranking when 2 then nation_name end  as na2
,case ranking when 2 then amt end as am2
,case ranking when 3 then nation_name end as na3
,case ranking when 3 then amt end as am3
from (select * from master
left join prefecture
using(pf_code)
left join nationality
using(nation_code)
)) group by pf_code, pf_name
)
left join total
using(pf_code)
order by ttl_amt desc