select P.PF_CODE AS 都道府県コード
      ,P.PF_NAME AS 都道府県名
      ,MAX(CASE WHEN JUNI = 1 THEN (SELECT NATION_NAME FROM NATIONALITY WHERE NATION_CODE = TARGET.NATION_CODE) ELSE NULL END) AS '1位 国名'
      ,SUM(CASE WHEN JUNI = 1 THEN AMT_SUM ELSE 0 END) AS '1位 人数'
      ,MAX(CASE WHEN JUNI = 2 THEN (SELECT NATION_NAME FROM NATIONALITY WHERE NATION_CODE = TARGET.NATION_CODE) ELSE NULL END) AS '2位 国名'
      ,SUM(CASE WHEN JUNI = 2 THEN AMT_SUM ELSE 0 END) AS '2位 人数'
      ,MAX(CASE WHEN JUNI = 3 THEN (SELECT NATION_NAME FROM NATIONALITY WHERE NATION_CODE = TARGET.NATION_CODE) ELSE NULL END) AS '3位 国名'
      ,SUM(CASE WHEN JUNI = 3 THEN AMT_SUM ELSE 0 END) AS '3位 人数'
      ,SUM(AMT_SUM) AS 合計人数
FROM (
select PF_CODE
      ,NATION_CODE
      ,AMT_SUM
      ,ROW_NUMBER() OVER(PARTITION BY PF_CODE ORDER BY AMT_SUM DESC, NATION_CODE) AS JUNI
from (
select
      PF_CODE
     ,NATION_CODE
     ,SUM(AMT) AS AMT_SUM
    FROM FOREIGNER
   WHERE NATION_CODE <> '113'
   GROUP BY PF_CODE, NATION_CODE
)
) TARGET
  inner join PREFECTURE P
    ON P.PF_CODE = TARGET.PF_CODE
group by 都道府県コード
order by 合計人数 DESC, 都道府県コード