WITH MAIN AS(
SELECT
 F.PF_CODE
,PR.PF_NAME
,F.NATION_CODE
,N.NATION_NAME
,AMT
,RANK() OVER(PARTITION BY F.PF_CODE ORDER BY AMT DESC) RNK
,SUM(AMT) OVER (PARTITION BY F.PF_CODE) ALLAMT
FROM
 FOREIGNER F
 INNER JOIN
 NATIONALITY N
 ON F.NATION_CODE = N.NATION_CODE
 INNER JOIN
 PREFECTURE PR
 ON F.PF_CODE = PR.PF_CODE
WHERE
 F.NATION_CODE <> '113'
)
SELECT
  M1.PF_CODE  都道府県コード
 ,M1.PF_NAME  都道府県名
 ,M1.NATION_NAME "1位 国名"
 ,M1.AMT "1位 人数"
 ,M2.NATION_NAME "2位 国名"
 ,M2.AMT "2位 人数"
 ,M3.NATION_NAME "3位 国名"
 ,M3.AMT "3位 人数"
 ,M1.ALLAMT 合計人数
FROM
  MAIN M1
  LEFT OUTER JOIN
  MAIN M2
  ON M1.PF_CODE =M2.PF_CODE 
  LEFT OUTER JOIN
  MAIN M3
  ON M2.PF_CODE =M3.PF_CODE 
WHERE 
  M1.RNK=1 AND M2.RNK=2 AND M3.RNK=3
ORDER BY M1.ALLAMT DESC,M1.PF_CODE
 ;