select
    PREFECTURE.PF_CODE as 都道府県コード,
    PREFECTURE.PF_NAME as 都道府県名,
    (select NATIONALITY.NATION_NAME
        from FOREIGNER
        inner join NATIONALITY
        on FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
        where FOREIGNER.PF_CODE = PREFECTURE.PF_CODE and FOREIGNER.NATION_CODE != 113
        order by FOREIGNER.AMT desc, FOREIGNER.NATION_CODE
        LIMIT 0, 1
    ) as "1位 国名",
    (select FOREIGNER.AMT
        from FOREIGNER
        where FOREIGNER.PF_CODE = PREFECTURE.PF_CODE and FOREIGNER.NATION_CODE != 113
        order by FOREIGNER.AMT desc, FOREIGNER.NATION_CODE
        LIMIT 0, 1
    ) as "1位 人数",
    (select NATIONALITY.NATION_NAME
        from FOREIGNER
        inner join NATIONALITY
        on FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
        where FOREIGNER.PF_CODE = PREFECTURE.PF_CODE and FOREIGNER.NATION_CODE != 113
        order by FOREIGNER.AMT desc, FOREIGNER.NATION_CODE
        LIMIT 1, 1
    ) as "2位 国名",
    (select FOREIGNER.AMT
        from FOREIGNER
        where FOREIGNER.PF_CODE = PREFECTURE.PF_CODE and FOREIGNER.NATION_CODE != 113
        order by FOREIGNER.AMT desc, FOREIGNER.NATION_CODE
        LIMIT 1, 1
    ) as "2位 人数",
    (select NATIONALITY.NATION_NAME
        from FOREIGNER
        inner join NATIONALITY
        on FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
        where FOREIGNER.PF_CODE = PREFECTURE.PF_CODE and FOREIGNER.NATION_CODE != 113
        order by FOREIGNER.AMT desc, FOREIGNER.NATION_CODE
        LIMIT 2, 1
    ) as "3位 国名",
    (select FOREIGNER.AMT
        from FOREIGNER
        where FOREIGNER.PF_CODE = PREFECTURE.PF_CODE and FOREIGNER.NATION_CODE != 113
        order by FOREIGNER.AMT desc, FOREIGNER.NATION_CODE
        LIMIT 2, 1
    ) as "3位 人数",
    (select sum(FOREIGNER.AMT)
        from FOREIGNER
        where FOREIGNER.PF_CODE = PREFECTURE.PF_CODE and FOREIGNER.NATION_CODE != 113
    ) as 合計人数
from PREFECTURE
where 合計人数 > 0
order by 合計人数 desc, PREFECTURE.PF_CODE