SELECT K.PF_CODE AS 都道府県コード
,K.PF_NAME AS 都道府県名
,MAX(CASE K.LANK WHEN 1 THEN K.NATION_NAME ELSE NULL END ) AS '1位 国名'
,MAX(CASE K.LANK WHEN 1 THEN K.AMT ELSE 0 END ) AS '1位 人数'
,MAX(CASE K.LANK WHEN 2 THEN K.NATION_NAME ELSE NULL END ) AS '2位 国名'
,MAX(CASE K.LANK WHEN 2 THEN K.AMT ELSE 0 END ) AS '2位 人数'
,MAX(CASE K.LANK WHEN 3 THEN K.NATION_NAME ELSE NULL END ) AS '3位 国名'
,MAX(CASE K.LANK WHEN 3 THEN K.AMT ELSE 0 END ) AS '3位 人数'
,SUM(K.AMT) AS '合計人数'
FROM FOREIGNER AS FOR
LEFT JOIN PREFECTURE AS PR
ON FOR.PF_CODE = PR.PF_CODE
LEFT JOIN NATIONALITY AS NA
ON FOR.NATION_CODE =NA.NATION_CODE 
INNER JOIN (SELECT F.PF_CODE AS PF_CODE
                   ,P.PF_NAME AS PF_NAME
                   ,F.NATION_CODE AS NATION_CODE
                   ,F.AMT AS AMT
                   ,N.NATION_NAME AS NATION_NAME
                   ,P.PF_NAME AS PF_NAME
                   ,RANK()OVER(PARTITION BY F.PF_CODE ORDER BY AMT DESC) AS LANK
            FROM FOREIGNER AS F
            LEFT JOIN PREFECTURE AS P
            ON F.PF_CODE = P.PF_CODE
            LEFT JOIN NATIONALITY AS N
            ON F.NATION_CODE=N.NATION_CODE
            WHERE F.NATION_CODE<>'113'
            ) AS K
ON K.PF_CODE =FOR.PF_CODE 
AND K.NATION_CODE=FOR.NATION_CODE
AND K.NATION_CODE<>'113'
WHERE FOR.NATION_CODE<>'113'
GROUP BY K.PF_CODE
,K.PF_NAME 
ORDER BY 合計人数 desc,
都道府県コード asc;