select PREFECTURE.PF_CODE as 都道府県コード, 
        PF_NAME as 都道府県名, 
        A.fst_nation as '1位 国名',	
        A.fst_amt as '1位 人数',
        A.sec_nation as '2位 国名',
        A.sec_amt as '2位 人数',
        A.trd_nation as '3位 国名',
        A.trd_amt as '3位 人数',
        B.合計人数 as 合計人数
    from (select PF_CODE, replace(group_concat(iif(順位=1, NATION_NAME, '')), ',', '') as fst_nation, 
        sum(iif(順位=1, 合計人数, 0)) as fst_amt, 
        replace(group_concat(iif(順位=2, NATION_NAME, '')), ',', '') as sec_nation, 
        sum(iif(順位=2, 合計人数, 0)) as sec_amt, 
        replace(group_concat(iif(順位=3, NATION_NAME, '')), ',', '') as trd_nation, 
        sum(iif(順位=3, 合計人数, 0)) as trd_amt
        from
        (select * from 
            (select PF_CODE , NATION_NAME, sum(AMT) as 合計人数, 
                rank() over(partition by PF_CODE order by AMT desc, NATIONALITY.NATION_CODE) as 順位
                from FOREIGNER, NATIONALITY on FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
                where NATIONALITY.NATION_CODE != '113'
                group by PF_CODE, NATIONALITY.NATION_CODE
                order by 合計人数 desc, NATIONALITY.NATION_CODE)
            where 順位 <= 3)
        group by PF_CODE) as A,
        (select PF_CODE, sum(AMT) as 合計人数 from FOREIGNER where NATION_CODE != '113'
            group by PF_CODE) as B,
    PREFECTURE on A.PF_CODE = PREFECTURE.PF_CODE
    where A.PF_CODE = B.PF_CODE
    order by 合計人数 desc, PREFECTURE.PF_CODE;