with r as (

select /*distinct*/ p.PF_CODE as 都道府県コード,
p.PF_NAME as 都道府県名,
(select AMT from FOREIGNER where PF_CODE = p.PF_CODE and NATION_CODE != '113' order by AMT desc limit 1) as '1位 人数',
(select AMT from FOREIGNER where PF_CODE = p.PF_CODE and NATION_CODE != '113' order by AMT desc limit 1 offset 1) as '2位 人数',
(select AMT from FOREIGNER where PF_CODE = p.PF_CODE and NATION_CODE != '113' order by AMT desc limit 1 offset 2) as '3位 人数',
--下でcrossjoinして、ここでnationalityとる選択肢もあるのかな
(select NATION_CODE from FOREIGNER where PF_CODE = p.PF_CODE and NATION_CODE != '113' order by AMT desc limit 1) as c1,
(select NATION_CODE from FOREIGNER where PF_CODE = p.PF_CODE and NATION_CODE != '113' order by AMT desc limit 1 offset 1) as c2,
(select NATION_CODE from FOREIGNER where PF_CODE = p.PF_CODE and NATION_CODE != '113' order by AMT desc limit 1 offset 2) as c3

from PREFECTURE p

--join FOREIGNER f
--on f.PF_CODE = p.PF_CODE
where (select AMT from FOREIGNER where PF_CODE = p.PF_CODE and NATION_CODE != '113' order by AMT desc limit 1) is not null
)

select r.都道府県コード,r.都道府県名,
n1.NATION_NAME as '1位 国名',r.'1位 人数',
n2.NATION_NAME as '2位 国名',r.'2位 人数',
n3.NATION_NAME as '3位 国名',r.'3位 人数',
sum(f.AMT) as 合計人数
from 
FOREIGNER f
left outer join r
on r.都道府県コード = f.PF_CODE
left outer join NATIONALITY n1
on r.c1 = n1.NATION_CODE
left outer join NATIONALITY n2
on r.c2 = n2.NATION_CODE
left outer join NATIONALITY n3
on r.c3 = n3.NATION_CODE

where f.NATION_CODE != '113' 

group by 
r.都道府県コード,r.都道府県名,
n1.NATION_NAME ,r.'1位 人数',
n2.NATION_NAME ,r.'2位 人数',
n3.NATION_NAME ,r.'3位 人数'

order by sum(f.AMT) desc