WITH tmp as (
	SELECT
		F.PF_CODE
		, P.PF_NAME
		, N.NATION_CODE
		, N.NATION_NAME
		, F.AMT
		, rank() over(partition by P.PF_CODE ORDER BY F.AMT desc, N.NATION_CODE) as rank_amt
	FROM FOREIGNER as F
		INNER JOIN PREFECTURE as P
			ON F.PF_CODE = P.PF_CODE
		INNER JOIN NATIONALITY as N
			ON F.NATION_CODE = N.NATION_CODE
	WHERE N.NATION_CODE <> '113'
	GROUP BY 1,2,3,4,5
)
SELECT
	t0.PF_CODE as '都道府県コード'
	, t0.PF_NAME as '都道府県名'
	, MAX(CASE when t0.rank_amt = 1 then t0.NATION_NAME end) as '1位 国名'
	, MAX(CASE when t0.rank_amt = 1 then t0.AMT end) as '1位 人数'
	, MAX(CASE when t0.rank_amt = 2 then t0.NATION_NAME end) as '2位 国名'
	, MAX(CASE when t0.rank_amt = 2 then t0.AMT end) as '2位 人数'
	, MAX(CASE when t0.rank_amt = 3 then t0.NATION_NAME end) as '3位 国名'
	, MAX(CASE when t0.rank_amt = 3 then t0.AMT end) as '3位 人数'
	, sum(t0.AMT) as '合計人数'
FROM tmp as t0
GROUP BY 1,2
ORDER BY 合計人数 desc, 都道府県コード