select 
    p.PF_CODE 都道府県コード,
    p.PF_NAME 都道府県名,
    onetbl.NATION_NAME "1位 国名",
    onetbl.AMT "1位 人数",
    twotbl.NATION_NAME "2位 国名",
    twotbl.AMT "2位 人数",
    thrtbl.NATION_NAME "3位 国名",
    thrtbl.AMT "3位 人数",
    tottbl.tot 合計人数
from
    (
    select 
        f.PF_CODE,n.NATION_NAME,f.AMT
    from
        FOREIGNER f
    inner join NATIONALITY n on f.NATION_CODE = n.NATION_CODE
    where n.NATION_CODE != '113'
    order by amt desc
    limit 1) onetbl
inner join
    (
    select 
        f.PF_CODE,n.NATION_NAME,f.AMT
    from
        FOREIGNER f
    inner join NATIONALITY n on f.NATION_CODE = n.NATION_CODE
    where n.NATION_CODE != '113'
    order by amt desc
    limit 1 offset 1) twotbl
on onetbl.PF_CODE = twotbl.PF_CODE

inner join
    (
    select 
        f.PF_CODE,n.NATION_NAME,f.AMT
    from
        FOREIGNER f
    inner join NATIONALITY n on f.NATION_CODE = n.NATION_CODE
    where n.NATION_CODE != '113'
    order by amt desc
    limit 1 offset 2) thrtbl
on onetbl.PF_CODE = thrtbl.PF_CODE

inner join
    (
    select pf_code,sum(amt) tot
    from foreigner 
    group by pf_code)tottbl
on onetbl.pf_code = tottbl.pf_code

inner join PREFECTURE p on onetbl.PF_CODE = p.PF_CODE

order by 合計人数 desc, 都道府県コード asc;