with AMT_RANK as
(
    select
        FOR.PF_CODE as PF_CODE
    ,   NAT.NATION_NAME as NATION_NAME
    ,   FOR.AMT as AMT
    ,   RANK() OVER(PARTITION BY FOR.PF_CODE ORDER BY FOR.AMT DESC, FOR.NATION_CODE) as RANK
    from
        FOREIGNER as FOR
    ,   NATIONALITY as NAT
    where
        FOR.NATION_CODE = NAT.NATION_CODE
    and FOR.NATION_CODE != '113'
)

select
    PRE.PF_CODE as 都道府県コード
,   PRE.PF_NAME as 都道府県名
,   FIRST.NATION_NAME as '1位 国名'
,   FIRST.AMT as '1位 人数'
,   SECOND.NATION_NAME as '2位 国名'
,   SECOND.AMT as '2位 人数'
,   THIRD.NATION_NAME as '3位 国名'
,   THIRD.AMT as '3位 人数'
,   TTL.AMT as 合計人数
from
    PREFECTURE as PRE
join
(
    select
        AMT_RANK.PF_CODE as PF_CODE
    ,   AMT_RANK.NATION_NAME as NATION_NAME
    ,   AMT_RANK.AMT as AMT
    from
        AMT_RANK
    where AMT_RANK.RANK = 1
) as FIRST
on PRE.PF_CODE = FIRST.PF_CODE
join
(
    select
        AMT_RANK.PF_CODE as PF_CODE
    ,   AMT_RANK.NATION_NAME as NATION_NAME
    ,   AMT_RANK.AMT as AMT
    from
        AMT_RANK
    where AMT_RANK.RANK = 2
) as SECOND
on PRE.PF_CODE = SECOND.PF_CODE
join
(
    select
        AMT_RANK.PF_CODE as PF_CODE
    ,   AMT_RANK.NATION_NAME as NATION_NAME
    ,   AMT_RANK.AMT as AMT
    from
        AMT_RANK
    where AMT_RANK.RANK = 3
) as THIRD
on PRE.PF_CODE = THIRD.PF_CODE
join
(
    select
        FOR.PF_CODE as PF_CODE
    ,   sum(FOR.AMT) as AMT
    from
        FOREIGNER as FOR
    where
        FOR.NATION_CODE != '113'
    group by
        FOR.PF_CODE
) as TTL
on PRE.PF_CODE = TTL.PF_CODE
order by
    TTL.AMT DESC
,   PRE.PF_CODE