with ss as (
select
    PF_CODE,
    sum(AMT) as AMT
from 
    FOREIGNER
group by
    PF_CODE
) 
,base as (
select
    PF_CODE,
    NATION_CODE,
    sum(AMT) as AMT
from 
    FOREIGNER
where
    NATION_CODE!="113"
group by
    PF_CODE,
    NATION_CODE
)
,base2 as (
select
    PF_CODE,
    NATION_CODE,
    AMT,
    RANK() OVER (PARTITION BY PF_CODE ORDER BY AMT DESC) as RANK_
from
    base
)
,rank1 as (
select
    b.PF_CODE,
    n.NATION_NAME,
    b.AMT
from
    base2 as b
    inner join
    NATIONALITY as n
    on b.NATION_CODE=n.NATION_CODE
WHERE RANK_=1
)
,rank2 as (
select
    b.PF_CODE,
    n.NATION_NAME,
    b.AMT
from
    base2 as b
    inner join
    NATIONALITY as n
    on b.NATION_CODE=n.NATION_CODE
WHERE RANK_=2
)
,rank3 as (
select
    b.PF_CODE,
    n.NATION_NAME,
    b.AMT
from
    base2 as b
    inner join
    NATIONALITY as n
    on b.NATION_CODE=n.NATION_CODE
WHERE RANK_=3
)
select
    pf.PF_CODE as `都道府県コード`,
    pf.PF_NAME as `都道府県名`,
    r1.NATION_NAME as `1位 国名`,
    r1.AMT as `1位 人数`,
    r2.NATION_NAME as `2位 国名`,
    r2.AMT as `2位 人数`,
    r3.NATION_NAME as `3位 国名`,
    r3.AMT as `3位 人数`,
    ss.AMT as `合計人数`
from
    PREFECTURE as pf
    INNER JOIN
    rank1 as r1
    ON pf.PF_CODE=r1.PF_CODE
    INNER JOIN
    rank2 as r2
    ON pf.PF_CODE=r2.PF_CODE
    INNER JOIN
    rank3 as r3
    ON pf.PF_CODE=r3.PF_CODE
    INNER JOIN
    ss
    ON pf.PF_CODE=ss.PF_CODE
order by
    ss.AMT desc,
    pf.PF_CODE