select
	PF_CODE as `都道府県コード`,
	PF_NAME as `都道府県名`,
	max(case when amt_rank = 1 then NATION_NAME else NULL end)	as `1位 国名`,
	sum(case when amt_rank = 1 then AMT else 0 end)				as `1位 人数`,
	max(case when amt_rank = 2 then NATION_NAME else NULL end)	as `2位 国名`,
	sum(case when amt_rank = 2 then AMT else 0 end)				as `2位 人数`,
	max(case when amt_rank = 3 then NATION_NAME else NULL end)	as `3位 国名`,
	sum(case when amt_rank = 3 then AMT else 0 end)				as `3位 人数`,
	sum(AMT) as `合計人数`
from
	(
		select
			*,
			rank() over(partition by t1.PF_CODE order by t1.AMT desc, t1.NATION_CODE) as amt_rank
		from
			FOREIGNER t1
		inner join
			NATIONALITY t2
			on  t1.NATION_CODE = t2.NATION_CODE
		inner join
			PREFECTURE t3
			on  t1.PF_CODE = t3.PF_CODE
		where
			t1.NATION_CODE != '113'
	)
group by 1,2
order by 9 desc, 1