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