with SUB 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 都道府県名
,   MAX(CASE SUB.RANK WHEN 1 THEN SUB.NATION_NAME END) as '1位 国名'
,   MAX(CASE SUB.RANK WHEN 1 THEN SUB.AMT END) as '1位 人数'
,   MAX(CASE SUB.RANK WHEN 2 THEN SUB.NATION_NAME END) as '2位 国名'
,   MAX(CASE SUB.RANK WHEN 2 THEN SUB.AMT END) as '2位 人数'
,   MAX(CASE SUB.RANK WHEN 3 THEN SUB.NATION_NAME END) as '3位 国名'
,   MAX(CASE SUB.RANK WHEN 3 THEN SUB.AMT END) as '3位 人数'
,   TTL.AMT as 合計人数
from
    PREFECTURE as PRE
join
    SUB
on
    PRE.PF_CODE = SUB.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
group by
    PRE.PF_CODE
order by
    TTL.AMT DESC
,   PRE.PF_CODE