SELECT 
	C.PF_CODE AS 都道府県コード
	,E.PF_NAME AS 都道府県名
	,MAX(CASE WHEN C.RANK = 1 THEN C.NATION_NAME END)  AS '1位 国名'
	,MAX(CASE WHEN C.RANK = 1 THEN C.AMT END)  AS '1位 人数'
	,MAX(CASE WHEN C.RANK = 2 THEN C.NATION_NAME END)  AS '2位 国名'
	,MAX(CASE WHEN C.RANK = 2 THEN C.AMT END)  AS '2位 人数'
 	,MAX(CASE WHEN C.RANK = 3 THEN C.NATION_NAME END)  AS '3位 国名'
	,MAX(CASE WHEN C.RANK = 3 THEN C.AMT END)   AS '3位 人数'
	,D.TOTAL AS 合計人数
FROM
(
	SELECT 
		A.PF_CODE
		,RANK() OVER(PARTITION BY A.PF_CODE ORDER BY A.AMT DESC,A.NATION_CODE) AS RANK
		,A.NATION_CODE
		,B.NATION_NAME
		,A.AMT
	FROM FOREIGNER A
	LEFT JOIN NATIONALITY B ON A.NATION_CODE = B.NATION_CODE
	WHERE A.NATION_CODE <> '113'
	ORDER BY A.PF_CODE ASC
) C
LEFT JOIN (
	SELECT PF_CODE,SUM(AMT) AS TOTAL FROM FOREIGNER WHERE NATION_CODE <> '113' GROUP BY PF_CODE
) D ON C.PF_CODE = D.PF_CODE
LEFT JOIN PREFECTURE E ON C.PF_CODE = E.PF_CODE
GROUP BY C.PF_CODE
ORDER BY D.TOTAL DESC ,  C.PF_CODE ASC