with ranking as(
    select
        p.PF_CODE,
        p.PF_NAME,
        f.NATION_CODE,
        n.NATION_NAME,
        f.AMT,
        rank() over (partition by p.PF_CODE order by f.AMT desc, f.NATION_CODE) as rank
    from
        PREFECTURE as p
    inner join
        FOREIGNER as f
        on p.PF_CODE = f.PF_CODE
    inner join
        NATIONALITY as n
        on f.NATION_CODE = n.NATION_CODE
    where
        f.NATION_CODE != "113"
)

select
    PF_CODE as `都道府県コード`,
    PF_NAME as `都道府県名`,
    max(case when rank = 1 then NATION_NAME end) as `1位 国名`,
    max(case when rank = 1 then AMT end) as `1位 人数`,
    max(case when rank = 2 then NATION_NAME end) as `2位 国名`,
    max(case when rank = 2 then AMT end) as `2位 人数`,
    max(case when rank = 3 then NATION_NAME end) as `3位 国名`,
    max(case when rank = 3 then AMT end) as `3位 人数`,
    sum(AMT) as `合計人数`
from
    ranking
group by
    1, 2
order by
    9 desc, 1