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
    (
        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'
    ) as 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
    (
        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'
    ) as 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
    (
        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'
    ) as 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