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 fr.pf_code
order by `合計人数` desc, `都道府県コード`