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