with t1 as (select pf_code
                 , NATION_CODE
                 , amt
                 , rank() over (
        partition by pf_code
        order by amt desc,NATION_CODE
        ) as rnk
            from FOREIGNER fo
            where NATION_CODE <> '113')

select t1.pf_code as '都道府県コード'
     , pf.PF_NAME as '都道府県名'
     , max(case when t1.rnk == 1 then na.NATION_NAME else '' end) '1位 国名'
     , max(case when t1.rnk == 1 then t1.amt else 0 end) '1位 人数'
     , max(case when t1.rnk == 2 then na.NATION_NAME else '' end) '2位 国名'
     , max(case when t1.rnk == 2 then t1.amt else 0 end) '2位 人数'
     , max(case when t1.rnk == 3 then na.NATION_NAME else '' end) '3位 国名'
     , max(case when t1.rnk == 3 then t1.amt else 0 end) '3位 人数'
     , max(tot.amt) '合計人数'
from t1
     join PREFECTURE pf on t1.PF_CODE = pf.PF_CODE
     join NATIONALITY na on t1.NATION_CODE = na.NATION_CODE
     join (select pf_code, sum(amt) amt
           from FOREIGNER
           where NATION_CODE <> '113'
           group by PF_CODE) tot on tot.PF_CODE = t1.pf_code

where rnk <= 3

group by t1.PF_CODE
order by 9 desc, 1
;