SELECT 
top1.PF_CODE '都道府県コード'
,p.PF_NAME '都道府県名'
,top1.NATION_NAME '1位 国名'
,top1.AMT '1位 人数'
,top2.NATION_NAME '2位 国名'
,top2.AMT'2位 人数'
,top3.NATION_NAME '3位 国名'
,top3.AMT'3位 人数'
,TOTAL_AMT '合計人数'
FROM
(
    SELECT *
    FROM
    (
        select 
        PF_CODE
        ,NATION_NAME
        ,AMT
        ,rank()OVER (PARTITION BY PF_CODE ORDER BY AMT desc,t1.NATION_CODE ) as Rank
        from FOREIGNER t1
        INNER JOIN
        NATIONALITY t2
        ON
        t1.NATION_CODE = t2.NATION_CODE
        where t1.NATION_CODE !='113'
    )a
    WHERE Rank=1
)top1
INNER JOIN
(
    SELECT *
    FROM
    (
        select 
        PF_CODE
        ,NATION_NAME
        ,AMT
        ,rank()OVER (PARTITION BY PF_CODE ORDER BY AMT desc,t1.NATION_CODE ) as Rank
        from FOREIGNER t1
        INNER JOIN
        NATIONALITY t2
        ON
        t1.NATION_CODE = t2.NATION_CODE
        where t1.NATION_CODE !='113'
    )a
    WHERE Rank=2
)top2
ON
top1.PF_CODE = top2.PF_CODE
INNER JOIN
(
    SELECT *
    FROM
    (
        select 
        PF_CODE
        ,NATION_NAME
        ,AMT
        ,rank()OVER (PARTITION BY PF_CODE ORDER BY AMT desc,t1.NATION_CODE) as Rank
        from FOREIGNER t1
        INNER JOIN
        NATIONALITY t2
        ON
        t1.NATION_CODE = t2.NATION_CODE
        where t1.NATION_CODE !='113'
    )a
    WHERE Rank=3
)top3
ON
top1.PF_CODE = top3.PF_CODE
INNER JOIN
(
    select 
    PF_CODE
    ,SUM(AMT) TOTAL_AMT
    from FOREIGNER
    where NATION_CODE !='113'
    GROUP BY 
    PF_CODE
)tot
ON
top1.PF_CODE = tot.PF_CODE
INNER JOIN
PREFECTURE p
on
top1.PF_CODE = p.PF_CODE
ORDER BY TOTAL_AMT desc
,top1.PF_CODE