select 
    PF_CODE AS '都道府県コード',
    PF_NAME AS '都道府県名',
    fnn AS '1位 国名',
    fa AS '1位 人数',
    snn AS '2位 国名',
    sa AS '2位 人数',
    tnn AS '3位 国名',
    ta AS '3位 人数',
    sum AS '合計人数'
    
    from
(select
    *
    from 
(select
    *
    from
    (select
        *
        from
        (select
            *
            from PREFECTURE as base
        inner join 
            (select
                PF_CODE AS fp,
                NATION_CODE AS fn,
                AMT AS fa
                from
                (select
                    PF_CODE,
                    NATION_CODE,
                    AMT,
                    row_number() over (partition by PF_CODE order by AMT desc) as rank
                    from
                    (select
                        *
                        from FOREIGNER
                        where NATION_CODE != 113)
                ) 
                where
                    rank = 1
            ) as first
        on
            base.PF_CODE =first.fp
        ) as base_first

        inner join 
            (select
                PF_CODE as sp,
                NATION_CODE as sn,
                AMT as sa
                from
                (select
                    PF_CODE,
                    NATION_CODE,
                    AMT,
                    row_number() over (partition by PF_CODE order by AMT desc) as rank
                    from 
                    (select
                        *
                        from FOREIGNER
                        where NATION_CODE != 113)
                ) 
                where
                    rank = 2
            ) as second
        on
            base_first.PF_CODE = second.sp
    ) as base_second
    
        inner join 
            (select
                PF_CODE as tp,
                NATION_CODE as tn,
                AMT as ta
                from
                (select
                    PF_CODE,
                    NATION_CODE,
                    AMT,
                    row_number() over (partition by PF_CODE  order by AMT desc) as rank
                    from
                    (select
                        *
                        from FOREIGNER
                        where NATION_CODE != 113)
                ) 
                where
                    rank = 3
            ) as third
            on base_second.PF_CODE = third.tp
    ) as base_third
    inner join
        (select 
            NATION_CODE, NATION_NAME AS fnn
            From NATIONALITY) as N1
    on base_third.fn = N1.NATION_CODE
    inner join
        (select 
            NATION_CODE, NATION_NAME AS snn
            From Nationality) as N2
    on base_third.sn = N2.NATION_CODE
    inner join
        (select 
            NATION_CODE, NATION_NAME AS tnn
            From Nationality) as N3
    on base_third.tn = N3.NATION_CODE
    inner join
        (select PF_CODE AS ap, SUM(AMT) as sum
            from FOREIGNER
            group by PF_CODE
            ) as sumall
    on base_third.PF_CODE = sumall.ap
    ) as base_table
    order by base_table.sum desc,base_table.PF_CODE ASC