SELECT A.PF_CODE           AS [都道府県コード]
      ,A.PF_NAME           AS [都道府県名]
      ,NATION1.NATION_NAME AS [1位 国名]
      ,B.AMT1              AS [1位 人数]
      ,NATION2.NATION_NAME AS [2位 国名]
      ,B.AMT2              AS [2位 人数]
      ,NATION3.NATION_NAME AS [3位 国名]
      ,B.AMT3              AS [3位 人数]
      ,B.ALLAMT            AS [合計人数] 
FROM PREFECTURE A
INNER JOIN 
(
   SELECT sub.PF_CODE 
         ,MAX(sub.NATION_CODE) FILTER(WHERE sub.Rank=1) AS NATION_CODE1
         ,MAX(sub.AMT) FILTER(WHERE sub.Rank=1) AS AMT1
         ,MAX(sub.NATION_CODE) FILTER(WHERE sub.Rank=2) AS NATION_CODE2
         ,MAX(sub.AMT) FILTER(WHERE sub.Rank=2) AS AMT2
         ,MAX(sub.NATION_CODE) FILTER(WHERE sub.Rank=3) AS NATION_CODE3
         ,MAX(sub.AMT) FILTER(WHERE sub.Rank=3) AS AMT3
         ,SUM(sub.AMT) AS ALLAMT
   FROM(
   SELECT a.PF_CODE
         ,a.NATION_CODE
         ,a.AMT
         ,ROW_NUMBER() OVER(PARTITION BY a.PF_CODE ORDER BY a.AMT DESC) AS Rank
   FROM FOREIGNER a
   WHERE a.NATION_CODE <> '113'
   )sub
   GROUP BY sub.PF_CODE
)B
ON A.PF_CODE = B.PF_CODE
INNER JOIN NATIONALITY NATION1
ON B.NATION_CODE1 = NATION1.NATION_CODE
INNER JOIN NATIONALITY NATION2
ON B.NATION_CODE2 = NATION2.NATION_CODE
INNER JOIN NATIONALITY NATION3
ON B.NATION_CODE3 = NATION3.NATION_CODE
ORDER BY B.ALLAMT DESC
        ,A.PF_CODE