Select
	p.pf_code as 都道府県コード,
	p.pf_name as 都道府県名,
	max(case when f.rk = 1 then n.nation_name end) as "1位 国名",
	sum(case when f.rk = 1 then f.amt end) as "1位 人数",
	max(case when f.rk = 2 then n.nation_name end) as "2位 国名",
	sum(case when f.rk =2 then f.amt end) as "2位 人数",
	max(case when f.rk = 3 then n.nation_name end) as "3位 国名",
	sum(case when f.rk = 3 then f.amt end) as "3位 人数",
	sum(f.amt) as 合計人数
From
	(select pf_code, nation_code, amt, RANK() OVER (partition by pf_code order by amt desc, nation_code asc) as rk from foreigner where nation_code != '113') as f
	inner join prefecture as p
		on (p.pf_code = f.pf_code)
	inner join nationality as n
		on (n.nation_code = f.nation_code)
Group by
	p.pf_code, p.pf_name
Order by
	9 desc, 1 asc