select
    fr.pf_code                                       `都道府県コード`,
    fr.pf_name                                       `都道府県名`,
    max(case fr.rank when 1 then fr.nation_name end) `1位 国名`,
    max(case fr.rank when 1 then fr.amt end)         `1位 人数`,
    max(case fr.rank when 2 then fr.nation_name end) `2位 国名`,
    max(case fr.rank when 2 then fr.amt end)         `2位 人数`,
    max(case fr.rank when 3 then fr.nation_name end) `3位 国名`,
    max(case fr.rank when 3 then fr.amt end)         `3位 人数`,
    sum(fr.amt)                                      `合計人数`
from (
    SELECT
        f.pf_code,
        p.pf_name,
        n.nation_name,
        f.amt,
        rank() over ( 
            partition by
                f.pf_code
            order by 
                f.amt desc, f.pf_code
        ) rank
    FROM
        foreigner f
        inner join prefecture p on p.pf_code=f.pf_code
        inner join nationality n on n.nation_code=f.nation_code
    WHERE
        f.nation_code != '113'
) fr
group by pf_code
order by `合計人数` desc, `都道府県コード`