SELECT
     T3.PF_CODE
    ,T4.PF_NAME
    ,MAX(CASE WHEN RANK3=1 THEN T3.NATION_NAME ELSE "" END) AS "1位 国名"
    ,SUM(CASE WHEN RANK3=1 THEN T3.SUM_AMT ELSE 0 END) AS "1位 人数"
    ,MAX(CASE WHEN RANK3=2 THEN T3.NATION_NAME ELSE "" END) AS "2位 国名"
    ,SUM(CASE WHEN RANK3=2 THEN T3.SUM_AMT ELSE 0 END) AS "2位 人数"
    ,MAX(CASE WHEN RANK3=3 THEN T3.NATION_NAME ELSE "" END) AS "3位 国名"
    ,SUM(CASE WHEN RANK3=3 THEN T3.SUM_AMT ELSE 0 END) AS "3位 人数"
    ,SUM(T3.SUM_AMT) AS "合計人数"
FROM
(
    SELECT
         T1.PF_CODE
        ,T1.NATION_CODE
        ,T2.NATION_NAME
        ,SUM(T1.AMT) AS SUM_AMT
        ,RANK() OVER(PARTITION BY T1.PF_CODE ORDER BY T1.PF_CODE, SUM(T1.AMT) DESC, T1.NATION_CODE) AS RANK3
    FROM
        FOREIGNER AS T1
    INNER JOIN
        NATIONALITY AS T2 ON T1.NATION_CODE=T2.NATION_CODE
    WHERE
        T1.NATION_CODE != '113'
    GROUP BY
        T1.PF_CODE,
        T1.NATION_CODE
    ORDER BY
        T1.PF_CODE,
        SUM(T1.AMT) DESC,
        T1.NATION_CODE
) AS T3
INNER JOIN
  PREFECTURE AS T4 ON T3.PF_CODE=T4.PF_CODE
GROUP BY
  T3.PF_CODE
ORDER BY
   SUM(T3.SUM_AMT) DESC
  ,T3.PF_CODE