select
    prefecture.pf_code as 都道府県コード,
    prefecture.pf_name as 都道府県名,
    sum(coalesce(foreigner.amt, 0)) as 合計人数,
    (
        select
            f1.nation_name
        from
        (
            select
                nationality.nation_name,
                foreigner.nation_code,
                foreigner.amt
            from
                foreigner
            inner join
                nationality
            on
                foreigner.nation_code = nationality.nation_code
            where
                foreigner.nation_code != '113'
                and foreigner.pf_code = prefecture.pf_code
            order by
                amt desc
                ,foreigner.nation_code asc
            limit 1
        ) as f1
        order by
            f1.amt asc
            ,f1.nation_code desc
        limit 1
    ) as '1位 国名',
    (
        select
            min(f1.amt)
        from
        (
            select
                foreigner.amt
            from
                foreigner
            where
                foreigner.nation_code != '113'
                and foreigner.pf_code = prefecture.pf_code
            order by
                amt desc
                ,foreigner.nation_code asc
            limit 1
        ) as f1
    ) as '1位 人数',
    (
        select
            f2.nation_name
        from
        (
            select
                nationality.nation_name,
                foreigner.nation_code,
                foreigner.amt
            from
                foreigner
            inner join
                nationality
            on
                foreigner.nation_code = nationality.nation_code
            where
                foreigner.nation_code != '113'
                and foreigner.pf_code = prefecture.pf_code
            order by
                amt desc
                ,foreigner.nation_code asc
            limit 2
        ) as f2
        order by
            f2.amt asc
            ,f2.nation_code desc
        limit 1
    ) as '2位 国名',
    (
        select
            min(f2.amt)
        from
        (
            select
                foreigner.amt
            from
                foreigner
            where
                foreigner.nation_code != '113'
                and foreigner.pf_code = prefecture.pf_code
            order by
                amt desc
                ,foreigner.nation_code asc
            limit 2
        ) as f2
    ) as '2位 人数',
    (
        select
            f3.nation_name
        from
        (
            select
                nationality.nation_name,
                foreigner.nation_code,
                foreigner.amt
            from
                foreigner
            inner join
                nationality
            on
                foreigner.nation_code = nationality.nation_code
            where
                foreigner.nation_code != '113'
                and foreigner.pf_code = prefecture.pf_code
            order by
                amt desc
                ,foreigner.nation_code asc
            limit 3
        ) as f3
        order by
            f3.amt asc
            ,f3.nation_code desc
        limit 1
    ) as '3位 国名',
    (
        select
            min(f3.amt)
        from
        (
            select
                foreigner.amt
            from
                foreigner
            where
                foreigner.nation_code != '113'
                and foreigner.pf_code = prefecture.pf_code
            order by
                amt desc
                ,foreigner.nation_code asc
            limit 3
        ) as f3
    ) as '3位 人数'
from
    prefecture
inner join
    foreigner
on
    prefecture.pf_code = foreigner.pf_code
and foreigner.nation_code != '113'
group by
    prefecture.pf_code
order by
    sum(coalesce(foreigner.amt, 0)) desc
    , prefecture.pf_code asc