SELECT FRN.PF_CODE AS 都道府県コード
,PF_NAME AS 都道府県名
,MAX(CASE WHEN RANK = 1 THEN NATION_NAME END) AS '1位 国名'
,MAX(CASE WHEN RANK = 1 THEN AMT END) AS '1位 人数'

,MAX(CASE WHEN RANK = 2 THEN NATION_NAME END) AS '2位 国名'
,MAX(CASE WHEN RANK = 2 THEN AMT END) AS '2位 人数'
,MAX(CASE WHEN RANK = 3 THEN NATION_NAME END) AS '3位 国名'
,MAX(CASE WHEN RANK = 3 THEN AMT END) AS '3位 人数'
,SUM(AMT) AS 合計人数
FROM (SELECT *
             ,NATION_NAME
             ,RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC,FOREIGNER.NATION_CODE) AS RANK 
      FROM FOREIGNER
      LEFT JOIN NATIONALITY
        ON FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
      WHERE FOREIGNER.NATION_CODE <> '113') FRN
      
LEFT JOIN PREFECTURE
  ON FRN.PF_CODE = PREFECTURE.PF_CODE
GROUP BY FRN.PF_CODE
ORDER BY 合計人数 DESC,都道府県コード