with tmp as(
select PF_CODE,
NATION_CODE,
AMT,
RANK() over(partition by PF_CODE ORDER BY AMT desc, NATION_CODE) as ranking
FROM FOREIGNER
WHERE NATION_CODE != 113
),
tmpsum as(
select PF_CODE,
SUM(AMT) as s
from FOREIGNER
WHERE NATION_CODE != 113
group by 1
),
tmp1 as(
SELECT PF_CODE,
t.NATION_CODE,
NATIONALITY.NATION_NAME, --as "1位 国名",
AMT --"1位 人数",
from tmp t
left join NATIONALITY ON t.NATION_CODE = NATIONALITY.NATION_CODE
where ranking = 1
),
tmp2 as(
SELECT PF_CODE,
t.NATION_CODE,
NATIONALITY.NATION_NAME,
AMT
from tmp t
left join NATIONALITY ON t.NATION_CODE = NATIONALITY.NATION_CODE
where ranking = 2
),
tmp3 as(
SELECT PF_CODE,
t.NATION_CODE,
NATIONALITY.NATION_NAME,
AMT
from tmp t
left join NATIONALITY ON t.NATION_CODE = NATIONALITY.NATION_CODE
where ranking = 3
)
select 
tmp1.PF_CODE as "都道府県コード",
p.PF_NAME as "都道府県名",
tmp1.NATION_NAME  as "1位 国名",
tmp1.AMT as "1位 人数",
tmp2.NATION_NAME  as "2位 国名",
tmp2.AMT as "2位 人数",
tmp3.NATION_NAME  as "3位 国名",
tmp3.AMT as "3位 人数",
tmpsum.s as "合計人数"
from tmp1
left join tmp2 on tmp1.PF_CODE = tmp2.PF_CODE
left join tmp3 on tmp1.PF_CODE = tmp3.PF_CODE
left join tmpsum on tmpsum.PF_CODE = tmp1.PF_CODE
left join PREFECTURE p on tmp1.PF_CODE = p.PF_CODE
order by 9 desc, 1