SELECT
	PF_CODE AS "都道府県コード"
	,PF_NAME AS "都道府県名"
	,MAX(CASE tmp.RANK WHEN 1 THEN NATION_NAME ELSE NULL END) AS "1位 国名"
	,MAX(CASE tmp.RANK WHEN 1 THEN AMT ELSE 0 END) AS "1位 人数"
	,MAX(CASE tmp.RANK WHEN 2 THEN NATION_NAME ELSE NULL END) AS "2位 国名"
	,MAX(CASE tmp.RANK WHEN 2 THEN AMT ELSE 0 END) AS "2位 人数"
	,MAX(CASE tmp.RANK WHEN 3 THEN NATION_NAME ELSE NULL END) AS "3位 国名"
	,MAX(CASE tmp.RANK WHEN 3 THEN AMT ELSE 0 END) AS "3位 人数"
	,SUM(AMT) AS "合計人数"
FROM
	(
		SELECT
			p.PF_CODE AS PF_CODE
			,p.PF_NAME AS PF_NAME 
			,n.NATION_CODE AS NATION_CODE 
			,n.NATION_NAME AS NATION_NAME 
			,f.AMT AS AMT
			, RANK() OVER (
				PARTITION BY
					p.PF_CODE
				ORDER BY
					f.AMT DESC
					, n.NATION_CODE ASC
			) AS RANK
		FROM
			FOREIGNER AS f
		INNER JOIN PREFECTURE AS p
			ON p.PF_CODE = f.PF_CODE
		INNER JOIN NATIONALITY AS n
			ON n.NATION_CODE = f.NATION_CODE
		WHERE
			f.NATION_CODE <> '113'
	)AS tmp
GROUP BY
    tmp.PF_CODE
ORDER BY
	合計人数 DESC
	,都道府県コード ASC;