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 合計人数
from foreigner
group by pf_code)

select * from(
select pf_code, pf_name
,max(na1) as '1位 国名'
,max(am1) as '1位 人数'
,max(na2) as '2位 国名'
,max(am2) as '2位 人数'
,max(na3) as '3位 国名'
,max(am3) as '3位 人数'
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 合計人数 desc