create temporary table TOP3 as select s.* from(select PF_CODE, NATION_CODE, AMT, RANK() over(partition by PF_CODE order by AMT desc, NATION_CODE asc) as RANK from FOREIGNER where NATION_CODE != "113") as s where s.RANK <= 3;

create temporary table first as select TOP3.PF_CODE, TOP3.NATION_CODE, NATION_NAME, AMT from TOP3 inner join NATIONALITY on TOP3.NATION_CODE = NATIONALITY.NATION_CODE where RANK = 1;
create temporary table second as select TOP3.PF_CODE, TOP3.NATION_CODE, NATION_NAME, AMT from TOP3 inner join NATIONALITY on TOP3.NATION_CODE = NATIONALITY.NATION_CODE where RANK = 2;
create temporary table third as select TOP3.PF_CODE, TOP3.NATION_CODE, NATION_NAME, AMT from TOP3 inner join NATIONALITY on TOP3.NATION_CODE = NATIONALITY.NATION_CODE where RANK = 3;

create temporary table total as select PF_CODE, sum(AMT) as "TOTAL" from FOREIGNER where NATION_CODE != "113" group by PF_CODE;
select TOP3.PF_CODE as "都道府県コード", PF_NAME as "都道府県名", first.NATION_NAME as "1位 国名", first.AMT as "1位 人数",
second.NATION_NAME as "2位 国名", second.AMT as "2位 人数", third.NATION_NAME as "3位 国名", third.AMT as "3位 人数",
total.TOTAL as "合計人数" from TOP3 inner join PREFECTURE on TOP3.PF_CODE = PREFECTURE.PF_CODE
inner join first on TOP3.PF_CODE = first.PF_CODE inner join second on TOP3.PF_CODE = second.PF_CODE inner join third on TOP3.PF_CODE = third.PF_CODE
inner join total on TOP3.PF_CODE = total.PF_CODE group by TOP3.PF_CODE order by total.TOTAL desc, TOP3.PF_CODE desc