with base as (
select
f.PF_CODE,
f.AMT,
n.NATION_NAME,
rank() over (PARTITION by f.PF_CODE order by f.AMT desc, f.NATION_CODE) as r
from FOREIGNER f
inner join NATIONALITY n
on f.NATION_CODE=n.NATION_CODE
where f.NATION_CODE<>'113'
),
total as (
select
PF_CODE,
sum(AMT) as AMT
from base
group by PF_CODE
)
select
b1.PF_CODE as "都道府県コード",
p.PF_NAME as "都道府県名",
b1.NATION_NAME as "1位 国名",
b1.AMT as "1位 人数",
b2.NATION_NAME as "2位 国名",
b2.AMT as "2位 人数",
b3.NATION_NAME as "3位 国名",
b3.AMT as "3位 人数",
t.AMT as "合計人数"
from base b1
inner join PREFECTURE p
on b1.PF_CODE=p.PF_CODE
inner join base b2
on b1.PF_CODE=b2.PF_CODE
and b2.r=2
inner join base b3
on b1.PF_CODE=b3.PF_CODE
and b3.r=3
inner join total t
on b1.PF_CODE=t.PF_CODE
where b1.r=1
order by t.AMT desc, b1.PF_CODE