select
  p.PF_CODE AS 都道府県コード
  ,p.PF_NAME AS 都道府県名
  ,FOREIGNER1.NATION_NAME AS "1位 国名"
  ,FOREIGNER1.AMT AS "1位 人数"
  ,FOREIGNER2.NATION_NAME AS "2位 国名"
  ,FOREIGNER2.AMT AS "2位 人数"
  ,FOREIGNER3.NATION_NAME AS "3位 国名"
  ,FOREIGNER3.AMT AS "3位 人数"
  ,sum_FOREIGNER.AMT AS 合計人数
from PREFECTURE p
  inner join (
    select
      sum(AMT) as AMT
      ,PF_CODE
      ,NATION_CODE
    from FOREIGNER
    where
      NATION_CODE <> '113'
    group by
      PF_CODE
  ) sum_FOREIGNER
    on p.PF_CODE = sum_FOREIGNER.PF_CODE
  inner join (
    SELECT
       f.PF_CODE
      ,f.NATION_CODE
      ,n.NATION_NAME
      ,f.AMT
      ,RANK() OVER(PARTITION BY f.PF_CODE ORDER BY f.AMT DESC) AS RANKK
    FROM FOREIGNER f
    inner join NATIONALITY n
      on f.NATION_CODE = n.NATION_CODE
    where
      f.NATION_CODE <> '113'
  ) FOREIGNER1
    on sum_FOREIGNER.PF_CODE = FOREIGNER1.PF_CODE
    and FOREIGNER1.RANKK = 1
    
  inner join (
    SELECT
       f.PF_CODE
      ,f.NATION_CODE
      ,n.NATION_NAME
      ,f.AMT
      ,RANK() OVER(PARTITION BY f.PF_CODE ORDER BY f.AMT DESC) AS RANKK
    FROM FOREIGNER f
    inner join NATIONALITY n
      on f.NATION_CODE = n.NATION_CODE
    where
      f.NATION_CODE <> '113'
  ) FOREIGNER2
    on sum_FOREIGNER.PF_CODE = FOREIGNER2.PF_CODE
    and FOREIGNER2.RANKK = 2
    
  inner join (
    SELECT
       f.PF_CODE
      ,f.NATION_CODE
      ,n.NATION_NAME
      ,f.AMT
      ,RANK() OVER(PARTITION BY f.PF_CODE ORDER BY f.AMT DESC) AS RANKK
    FROM FOREIGNER f
    inner join NATIONALITY n
      on f.NATION_CODE = n.NATION_CODE
    where
      f.NATION_CODE <> '113'
  ) FOREIGNER3
    on sum_FOREIGNER.PF_CODE = FOREIGNER3.PF_CODE
    and FOREIGNER3.RANKK = 3
order by
  sum_FOREIGNER.AMT desc
  ,p.PF_CODE asc