WITH rank_base as (
select
    PF_CODE,
    f.NATION_CODE,
    n.NATION_NAME,
    AMT,
    RANK() OVER (PARTITION BY PF_CODE ORDER BY AMT desc,f.NATION_CODE) as RANK_
from
    FOREIGNER as f
    inner join
    NATIONALITY as n
    on f.NATION_CODE=n.NATION_CODE
where
    f.NATION_CODE!="113"
)
select
    rb.PF_CODE as `都道府県コード`,
    PF_NAME as `都道府県名`,
    MAX(CASE WHEN RANK_=1 THEN NATION_NAME ELSE NULL END) as `1位 国名`,
    MAX(CASE WHEN RANK_=1 THEN AMT ELSE 0 END) as `1位 人数`,
    MAX(CASE WHEN RANK_=2 THEN NATION_NAME ELSE NULL END) as `2位 国名`,
    MAX(CASE WHEN RANK_=2 THEN AMT ELSE 0 END) as `2位 人数`,
    MAX(CASE WHEN RANK_=3 THEN NATION_NAME ELSE NULL END) as `3位 国名`,
    MAX(CASE WHEN RANK_=3 THEN AMT ELSE 0 END) as `3位 人数`,
    sum(AMT) as `合計人数`
from
    rank_base as rb
    INNER JOIN
    PREFECTURE as pf
    ON rb.PF_CODE=pf.PF_CODE
group by
    rb.PF_CODE
order by
    sum(AMT) desc,
    rb.PF_CODE