select
fr_rank.pf_code AS "都道府県コード",
fr_rank.pf_name AS "都道府県名",
MAX(CASE WHEN fr_rank.amt_rank = 1 THEN fr_rank.nation_name ELSE NULL END) AS "1位 国名",
MAX(CASE WHEN fr_rank.amt_rank = 1 THEN fr_rank.amt_sum ELSE NULL END) AS "1位 人数",
MAX(CASE WHEN fr_rank.amt_rank = 2 THEN fr_rank.nation_name ELSE NULL END) AS "2位 国名",
MAX(CASE WHEN fr_rank.amt_rank = 2 THEN fr_rank.amt_sum ELSE NULL END) AS "2位 人数",
MAX(CASE WHEN fr_rank.amt_rank = 3 THEN fr_rank.nation_name ELSE NULL END) AS "3位 国名",
MAX(CASE WHEN fr_rank.amt_rank = 3 THEN fr_rank.amt_sum ELSE NULL END) AS "3位 人数",
SUM(fr_rank.amt_sum) AS "合計人数"
from
(select
f.pf_code,
p.pf_name,
n.nation_name,
SUM(f.amt) AS "amt_sum",
ROW_NUMBER() OVER (PARTITION BY f.pf_code ORDER BY SUM(f.amt) DESC ) AS "amt_rank"
from
foreigner as f
INNER JOIN nationality AS n ON f.nation_code = n.nation_code
INNER JOIN prefecture AS p ON p.pf_code = f.pf_code
where
n.nation_code <> '113'

group by f.pf_code, n.nation_name) AS fr_rank
group by fr_rank.pf_code
Order by "合計人数" desc , fr_rank.pf_code asc