select 
  t1.PF_CODE 都道府県コード,
  m1.PF_NAME 都道府県名,
  t1.NATION_NAME '1位 国名',
  t1.AMT '1位人数',
  t2.NATION_NAME '2位 国名',
  t2.AMT '2位人数',
  t3.NATION_NAME '3位 国名',
  t3.AMT '3位人数',
  t4.SUM_AMT 合計人数
from (
  select * from (select t.PF_CODE, t.NATION_CODE, m.NATION_NAME, DENSE_RANK() OVER (PARTITION by t.PF_CODE ORDER BY t.AMT DESC) RANK, t.NATION_CODE, t.AMT from FOREIGNER t left join NATIONALITY m on m.NATION_CODE = t.NATION_CODE where t.NATION_CODE <> '113') where RANK = 1
) t1
left join (
  select * from (select t.PF_CODE, t.NATION_CODE, m.NATION_NAME, DENSE_RANK() OVER (PARTITION by t.PF_CODE ORDER BY t.AMT DESC) RANK, t.NATION_CODE, t.AMT from FOREIGNER t left join NATIONALITY m on m.NATION_CODE = t.NATION_CODE where t.NATION_CODE <> '113') where RANK = 2
) t2
  on t2.PF_CODE = t1.PF_CODE
left join (
  select * from (select t.PF_CODE, t.NATION_CODE, m.NATION_NAME, DENSE_RANK() OVER (PARTITION by t.PF_CODE ORDER BY t.AMT DESC) RANK, t.NATION_CODE, t.AMT from FOREIGNER t left join NATIONALITY m on m.NATION_CODE = t.NATION_CODE where t.NATION_CODE <> '113') where RANK = 3
) t3 
  on t3.PF_CODE = t1.PF_CODE
left join (
  select PF_CODE, SUM(AMT) SUM_AMT from FOREIGNER where NATION_CODE <> '113' group by PF_CODE
) t4
  on t4.PF_CODE = t1.PF_CODE
left join PREFECTURE m1
  on m1.PF_CODE = t1.PF_CODE
order by 
  合計人数 desc,
  都道府県コード