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