SELECT
	 pf_code 都道府県コード
	,pf_name 都道府県名
	,MIN(CASE R
		WHEN 1 THEN nation_name
	END) '1位 国名'
	,MIN(CASE R
		WHEN 1 THEN AMT
	END) '1位 人数'
	,MIN(CASE R
		WHEN 2 THEN nation_name
	END) '2位 国名'
	,MIN(CASE R
		WHEN 2 THEN AMT
	END) '2位 人数'
	,MIN(CASE R
		WHEN 3 THEN nation_name
	END) '3位 国名'
	,MIN(CASE R
		WHEN 3 THEN AMT
	END) '3位 人数'
	,SUM(AMT) 合計人数
FROM	
(
	SELECT
		P.pf_code pf_code
		,P.pf_name pf_name
		,SUM(F.amt) AMT
		,N.nation_name nation_name
		,RANK() OVER (
			PARTITION BY
				P.pf_code
			ORDER BY 
				 AMT DESC
				,N.nation_code
		) R
	FROM
		Foreigner F
	INNER JOIN
		Prefecture P
	ON
		F.pf_code = P.pf_code
	INNER JOIN
		NATIONALITY N
	ON
		N.nation_code = F.nation_code
	WHERE
		N.nation_code <> '113'
	GROUP BY
		N.nation_code, P.pf_code
	ORDER BY 
		P.pf_code
) data
GROUP BY	
	pf_code
ORDER BY
	 合計人数 DESC
	,都道府県コード