with tbl as(
select
    na.NATION_NAME
    ,pr.PF_NAME
    ,fo.*
    , ROW_NUMBER() OVER(PARTITION BY pr.PF_CODE ORDER BY fo.AMT DESC) as rank
from
    FOREIGNER fo
LEFT JOIN
    NATIONALITY na
on
    na.NATION_CODE = fo.NATION_CODE
LEFT JOIN
    PREFECTURE pr
on
    pr.PF_CODE = fo.PF_CODE
WHERE
    fo.NATION_CODE != 113
)
, tbl_grped as(
select
    PF_CODE as "都道府県コード"
    ,PF_NAME as "都道府県名"
    ,SUM(AMT) as "合計人数"
from
    tbl
group BY
    1, 2
)
select
    grp.都道府県コード
    , grp.都道府県名
    ,fir.NATION_NAME as "1位 国名"
    ,fir.AMT as "1位 人数"
    ,scd.NATION_NAME as "2位 国名"
    ,scd.AMT as "2位 人数"
    ,thr.NATION_NAME as "3位 国名"
    ,thr.AMT as "3位 人数"
    ,grp.合計人数
from
    tbl_grped as grp
left JOIN
    (select * from tbl where rank = 1) as fir
on
    fir.PF_CODE = grp.都道府県コード
left JOIN
    (select * from tbl where rank = 2) as scd
on
    scd.PF_CODE = grp.都道府県コード
left JOIN
    (select * from tbl where rank = 3) as thr
on
    thr.PF_CODE = grp.都道府県コード
order BY
    9 DESC
    ,1
;