select
    A.PF_CODE as '都道府県コード',
    PF_NAME as '都道府県名' ,
MAX(case when A.rnk=1 then NATION_NAME else '' end) as '1位 国名',
MAX(case when A.rnk=1 then A.AMT else NULL end) as  '1位 人数',
    MAX(case when A.rnk=2 then NATION_NAME else '' end) as '2位 国名',
        MAX(case when A.rnk=2 then A.AMT else NULL end) as  '2位 人数',
    MAX(case when A.rnk=3 then NATION_NAME else '' end) as '3位 国名',
        MAX(case when A.rnk=3 then A.AMT else NULL end) as  '3位 人数',
    MAX(F.AMT) as '合計人数'

from (
select PF_CODE, NATION_CODE, AMT,rank() over (partition by PF_CODE order by amt desc, NATION_CODE) rnk
      from (select * from FOREIGNER where NATION_CODE <> '113') as A
      ) as A
inner join NATIONALITY on NATIONALITY.NATION_CODE =A.NATION_CODE
                       inner join PREFECTURE on PREFECTURE.PF_CODE=A.PF_CODE
left join (select PF_CODE ,SUM(AMT) as amt from FOREIGNER where NATION_CODE <> '113' group by PF_CODE) F on F.PF_CODE = A.PF_CODE
where A.rnk <= 3
group by A.PF_CODE
order by 9 desc, 1
;