SELECT K.PF_CODE AS 都道府県コード
,K.PF_NAME AS 都道府県名
,MAX(CASE K.NUM WHEN 1 THEN K.NATION_NAME ELSE NULL END) AS '1位 国名'
,MAX(CASE K.NUM WHEN 1 THEN K.AMT ELSE 0 END) AS '1位 人数'
,MAX(CASE K.NUM WHEN 2 THEN K.NATION_NAME ELSE NULL END) AS '2位 国名'
,MAX(CASE K.NUM WHEN 2 THEN K.AMT ELSE 0 END) AS '2位 人数'
,MAX(CASE K.NUM WHEN 3 THEN K.NATION_NAME ELSE NULL END) AS '3位 国名'
,MAX(CASE K.NUM WHEN 3 THEN K.AMT ELSE 0 END) AS '3位 人数'
,SUM(AMT) AS  合計人数
FROM (SELECT F.PF_CODE AS PF_CODE
             ,P.PF_NAME AS PF_NAME
             ,F.NATION_CODE AS NATION_CODE
             ,N.NATION_NAME AS NATION_NAME
             ,SUM(F.AMT) AS AMT
             ,RANK() OVER(partition by F.PF_CODE ORDER BY SUM(F.AMT) DESC,F.NATION_CODE DESC) AS NUM
        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
        WHERE F.NATION_CODE <> '113'
        GROUP BY F.PF_CODE,P.PF_NAME,F.NATION_CODE,N.NATION_NAME) AS K
WHERE NATION_CODE <>'113'
GROUP BY K.PF_CODE,K.PF_NAME
ORDER BY 合計人数 DESC,都道府県コード ASC