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 desc) 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
	F.PF_CODE as '都道府県コード'
	, P.PF_NAME as '都道府県名'
	, MAX(CASE when t1.NATION_NAME is not null then t1.NATION_NAME else 0 end) as '1位 国名'
	, MAX(CASE when t1.AMT is not null then t1.AMT else 0 end) as '1位 人数'
	, MAX(CASE when t2.NATION_NAME is not null then t2.NATION_NAME else 0 end) as '2位 国名'
	, MAX(CASE when t2.AMT is not null then t2.AMT else 0 end) as '2位 人数'
	, MAX(CASE when t3.NATION_NAME is not null then t3.NATION_NAME else 0 end) as '3位 国名'
	, MAX(CASE when t3.AMT is not null then t3.AMT else 0 end) as '3位 人数'
	, sum(F.AMT) as '合計人数'
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
-- 1位
LEFT JOIN tmp as t1
	on F.PF_CODE = t1.PF_CODE
	and F.NATION_CODE = t1.NATION_CODE
	and t1.rank_amt = 1
-- 2位
LEFT JOIN tmp as t2
	on F.PF_CODE = t2.PF_CODE
	and F.NATION_CODE = t2.NATION_CODE
	and t2.rank_amt = 2
-- 3位
LEFT JOIN tmp as t3
	on F.PF_CODE = t3.PF_CODE
	and F.NATION_CODE = t3.NATION_CODE
	and t3.rank_amt = 3
WHERE N.NATION_CODE <> '113'
GROUP BY 1,2
ORDER BY 合計人数 desc, 都道府県コード