select
tmp.PF_CODE as "都道府県コード",
max(PF_NAME) as "都道府県名",
max(case tmp.rank when 1 then tmp.NATION_NAME else null end) as "1位 国名",
max(case tmp.rank when 1 then tmp.AMT else null end) as "1位 人数",
max(case tmp.rank when 2 then tmp.NATION_NAME else null end) as "2位 国名",
max(case tmp.rank when 2 then tmp.AMT else null end) as "2位 人数",
max(case tmp.rank when 3 then tmp.NATION_NAME else null end) as "3位 国名",
max(case tmp.rank when 3 then tmp.AMT else null end) as "3位 人数",
max(SUM_AMT) as "合計人数"
from
(
select
p.PF_CODE,
PF_NAME,
NATION_NAME,
f.AMT,
rank() over(partition by p.PF_CODE order by AMT desc) as RANK
from PREFECTURE as p
join FOREIGNER as f on p.PF_CODE = f.PF_CODE and f.NATION_CODE <> 113
left join NATIONALITY as n on f.NATION_CODE = n.NATION_CODE
) as tmp
left join (
select
PF_CODE,
sum(AMT) as SUM_AMT
from FOREIGNER
group by PF_CODE
) as s on s.PF_CODE = tmp.PF_CODE
group by tmp.PF_CODE
order by SUM_AMT desc, tmp.PF_CODE