SELECT
	 F.PF_CODE AS 都道府県コード
	,RANKING.PF_NAME AS 都道府県名
	,RANKING.NATION_NAME  AS [1位 国名]
	,SUM(CASE WHEN RANKING.JUNI = 1 THEN F.AMT ELSE 0 END) AS [1位 人数]
	,RANKING.NATION_NAME AS [2位 国名]
	,SUM(CASE WHEN RANKING.JUNI = 2 THEN F.AMT ELSE 0 END) AS [2位 人数]
	,RANKING.NATION_NAME AS [3位 国名]
	,SUM(CASE WHEN RANKING.JUNI = 3 THEN F.AMT ELSE 0 END)  AS [3位 人数]
	,SUM(CASE WHEN RANKING.JUNI IN (1,2,3) THEN F.AMT ELSE 0 END) AS [合計人数]
FROM FOREIGNER AS F
INNER JOIN
(
	SELECT
		 F.PF_CODE
		,F.NATION_CODE 
		,P.PF_NAME
		,N.NATION_NAME
		,RANK() OVER ( PARTITION BY F.PF_CODE ORDER BY SUM( AMT ) DESC ) AS JUNI
		,SUM(AMT)
	FROM FOREIGNER AS F
	INNER JOIN PREFECTURE AS P
	ON  F.PF_CODE = P.PF_CODE
	INNER JOIN NATIONALITY AS N
	ON  F.NATION_CODE = N.NATION_CODE
	GROUP BY F.PF_CODE
		,F.NATION_CODE 
		,P.PF_NAME
		,N.NATION_NAME
) AS RANKING
ON  F.PF_CODE  = RANKING.PF_CODE
AND F.NATION_CODE = RANKING.NATION_CODE
GROUP BY F.PF_CODE,RANKING.PF_NAME,RANKING.NATION_NAME