with base as(
select
    A.PF_CODE as 都道府県コード
,   C.PF_NAME as 都道府県名
,   B.NATION_NAME as 国名
,   A.AMT as 人数
,   RANK () OVER (partition by A.PF_CODE order by A.AMT DESC)  as 順位
from
    FOREIGNER A
,   NATIONALITY B
,   PREFECTURE C
where
    A.NATION_CODE = B.NATION_CODE    
and A.PF_CODE = C.PF_CODE
and A.NATION_CODE <> 113
),
ichi as(
select
    A.都道府県コード
,   A.国名
,   A.人数
from
    base A
where
    A.順位 = 1
),
ni as(
select
    A.都道府県コード
,   A.国名
,   A.人数
from
    base A
where
    A.順位 = 2
),
san as(
select
    A.都道府県コード
,   A.国名
,   A.人数
from
    base A
where
    A.順位 = 3
),
goukei as(
select
    A.都道府県コード
,   SUM(A.人数) as 合計人数
from
    base A
group by
    A.都道府県コード
)
select distinct
    A.都道府県コード
,   A.都道府県名
,   B.国名 as '1位 国名'
,   B.人数 as '1位 人数'
,   C.国名 as '2位 国名'
,   C.人数 as '2位 人数'
,   D.国名 as '3位 国名'
,   D.人数 as '3位 人数'
,   E.合計人数
from
    base A
,   ichi B
,   ni C
,   san D
,   goukei E
where
    A.都道府県コード = B.都道府県コード
and A.都道府県コード = C.都道府県コード
and A.都道府県コード = D.都道府県コード
and A.都道府県コード = E.都道府県コード
order by
    E.合計人数 DESC
,   A.都道府県コード