with tmp as (
select
    f.PF_code,
    p.PF_NAME,
    n.NATION_NAME,
    f.AMT,
    row_number() over(partition by f.PF_CODE order by f.AMT desc) as i
from
    FOREIGNER as f
left join
    NATIONALITY as n on f.NATION_CODE = n.NATION_CODE
left join
    PREFECTURE as p on f.PF_CODE = p.PF_CODE
where
    f.NATION_CODE != "113"
)
select
    PF_code as 都道府県コード,
    PF_NAME as 都道府県名,
    max(case when i = 1 then NATION_NAME else null end) as `1位 国名`,
    sum(case when i = 1 then AMT else null end) as `1位 人数`,
    max(case when i = 2 then NATION_NAME else null end) as `2位 国名`,
    sum(case when i = 2 then AMT else null end) as `2位 人数`,
    max(case when i = 3 then NATION_NAME else null end) as `3位 国名`,
    sum(case when i = 3 then AMT else null end) as `3位 人数`,
    sum(AMT) as 合計人数
from
    tmp
group by
    PF_NAME
having
    i < 4
order by
    合計人数 DESC,
    都道府県コード ASC
;