with 
    sub as (
        select
            PF_CODE
            ,NATION_CODE
            ,AMT
            ,RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS ranking
        from FOREIGNER
        where NATION_CODE != '113'
        group by NATION_CODE,PF_CODE
        --order by PF_CODE,NATION_CODE
        )
    
    ,sum_amt as (
        select
            PF_CODE
            ,SUM(AMT) as AMT
        from FOREIGNER
        where NATION_CODE != '113'
        group by PF_CODE
        )
select
    sub.PF_CODE as 都道府県コード
    ,PREFECTURE.PF_NAME as 都道府県名
    ,MAX(CASE sub.ranking WHEN 1 THEN NATIONALITY.NATION_NAME ELSE NULL END) as "1位 国名"
    ,MAX(CASE sub.ranking WHEN 1 THEN sub.AMT ELSE 0 END) as "1位 人数"
    ,MAX(CASE sub.ranking WHEN 2 THEN NATIONALITY.NATION_NAME ELSE NULL END) as "2位 国名"
    ,MAX(CASE sub.ranking WHEN 2 THEN sub.AMT ELSE 0 END) as "2位 人数"
    ,MAX(CASE sub.ranking WHEN 3 THEN NATIONALITY.NATION_NAME ELSE NULL END) as "3位 国名"
    ,MAX(CASE sub.ranking WHEN 3 THEN sub.AMT ELSE 0 END) as "3位 人数"
    ,sum_amt.AMT as 合計人数
from
    sub
    inner join PREFECTURE
        on sub.PF_CODE = PREFECTURE.PF_CODE
    inner join NATIONALITY
        on sub.NATION_CODE = NATIONALITY.NATION_CODE
    left outer join sum_amt
        on sub.PF_CODE = sum_amt.PF_CODE
group by 都道府県コード
order by 
    合計人数 desc
    ,都道府県コード asc