SELECT
PF_CODE as "都道府県コード"
, PF_NAME as "都道府県名"
, MAX(CASE WHEN _rank = 1 THEN NATION_NAME ELSE NULL END) as "1位 国名"
, SUM(CASE WHEN _rank = 1 THEN AMT ELSE 0 END) as "1位 人数"
, MAX(CASE WHEN _rank = 2 THEN NATION_NAME ELSE NULL END) as "2位 国名"
, SUM(CASE WHEN _rank = 2 THEN AMT ELSE 0 END) as "2位 人数"
, MAX(CASE WHEN _rank = 3 THEN NATION_NAME ELSE NULL END) as "3位 国名"
, SUM(CASE WHEN _rank = 3 THEN AMT ELSE 0 END) as "3位 人数"
, SUM(AMT) as "合計人数"

FROM
(
SELECT
f.PF_CODE
, p.PF_NAME
, f.AMT
, n.NATION_NAME
, ROW_NUMBER() over (partition by f.PF_CODE order by f.amt desc, f.NATION_CODE) as _rank

FROM
FOREIGNER as f

INNER JOIN
PREFECTURE as p
on
f.PF_CODE = p.PF_CODE

INNER JOIN
NATIONALITY as n
on
f.NATION_CODE = n.NATION_CODE

WHERE
f.NATION_CODE != '113'
)

GROUP BY 
1,2

ORDER BY 
9 desc
, 1