with base as (
    SELECT
        f.pf_code
    ,   n.nation_name
    ,   rank() OVER (partition by f.pf_code ORDER BY sum(f.amt) DESC, f.nation_code) as rnk
    ,   sum(f.amt) as num
    ,   sum(f.amt) OVER (partition by pf_code) as total
    FROM
        foreigner as f
        INNER JOIN
            nationality as n
            on f.nation_code = n.nation_code
    WHERE
        f.nation_code != '113'
    GROUP BY
        f.pf_code
    ,   f.nation_code
)
select
    base.pf_code AS 都道府県コード
,   p.pf_name AS 都道府県名
,   MAX(CASE rnk WHEN 1 THEN nation_name ELSE NULL END) AS `1位 国名`
,   MAX(CASE rnk WHEN 1 THEN num ELSE NULL END) AS `1位 人数`
,   MAX(CASE rnk WHEN 2 THEN nation_name ELSE NULL END) AS `2位 国名`
,   MAX(CASE rnk WHEN 2 THEN num ELSE NULL END) AS `2位 人数`
,   MAX(CASE rnk WHEN 3 THEN nation_name ELSE NULL END) AS `3位 国名`
,   MAX(CASE rnk WHEN 3 THEN num ELSE NULL END) AS `3位 人数`
,   total AS 合計人数
from
    base
    inner join
        prefecture as p
        on base.pf_code = p.pf_code
where
    rnk <= 3
group by
    base.pf_code
ORDER BY
    合計人数 DESC
,   都道府県コード