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