SELECT
	  a2.PF_CODE "都道府県コード"
	, c.PF_NAME "都道府県名"
	, max( 
		CASE 
			WHEN a2.rank_amt = 1 
				THEN b.NATION_NAME 
			ELSE '' 
			END
	) "1位 国名"
	, sum( 
		CASE 
			WHEN a2.rank_amt = 1 
				THEN a2.max_amt 
			ELSE 0 
			END
	) "1位 人数"
	, max( 
		CASE 
			WHEN a2.rank_amt = 2 
				THEN b.NATION_NAME 
			ELSE '' 
			END
	) "2位 国名"
	, sum( 
		CASE 
			WHEN a2.rank_amt = 2 
				THEN a2.max_amt 
			ELSE 0 
			END
	) "2位 人数"
	, max( 
		CASE 
			WHEN a2.rank_amt = 3 
				THEN b.NATION_NAME 
			ELSE '' 
			END
	) "3位 国名"
	, sum( 
		CASE 
			WHEN a2.rank_amt = 3 
				THEN a2.max_amt 
			ELSE 0 
			END
	) "3位 人数"
	, a2.max_pfamt "合計人数" 
FROM
	( 
		SELECT
			  a.PF_CODE
			, a.NATION_CODE
			, sum(a.AMT) max_amt
			, sum(a.AMT) OVER (PARTITION BY a.PF_CODE) max_pfamt
			, ROW_NUMBER() OVER ( 
				PARTITION BY
					a.PF_CODE 
				ORDER BY
					sum(a.AMT) DESC
					, a.NATION_CODE
			) rank_amt 
		FROM
			FOREIGNER a 
		WHERE
			a.NATION_CODE != '113' 
		GROUP BY
			a.PF_CODE
			, a.NATION_CODE
	) a2 
	LEFT JOIN NATIONALITY b 
		ON a2.NATION_CODE = b.NATION_CODE 
	LEFT JOIN PREFECTURE c 
		ON a2.PF_CODE = c.PF_CODE 
GROUP BY
	a2.PF_CODE 
ORDER BY
	a2.max_pfamt DESC
	, a2.PF_CODE;