CREATE TEMPORARY TABLE temp1 AS
SELECT FOREIGNER.PF_CODE, SUM(FOREIGNER.AMT) AS am,ROW_NUMBER() OVER(PARTITION BY FOREIGNER.PF_CODE) AS ind, NATIONALITY.NATION_CODE, NATIONALITY.NATION_NAME
FROM FOREIGNER
JOIN PREFECTURE ON PREFECTURE.PF_CODE = FOREIGNER.PF_CODE
JOIN NATIONALITY ON NATIONALITY.NATION_CODE = FOREIGNER.NATION_CODE
WHERE FOREIGNER.NATION_CODE <> '113'
group by FOREIGNER.PF_CODE, NATIONALITY.NATION_CODE
order by am desc, FOREIGNER.NATION_CODE desc;

CREATE TEMPORARY TABLE temp2 AS
SELECT FOREIGNER.PF_CODE, SUM(FOREIGNER.AMT) as su
FROM FOREIGNER
WHERE FOREIGNER.NATION_CODE <> '113'
GROUP BY FOREIGNER.PF_CODE;

SELECT
p.PF_CODE AS "都道府県コード",
p.PF_NAME AS "都道府県名",
(SELECT temp1.NATION_NAME FROM temp1 where temp1.PF_CODE = p.PF_CODE AND ind = 1) AS "1位 国名",
(SELECT temp1.am FROM temp1 where temp1.PF_CODE = p.PF_CODE AND ind = 1) AS "1位 人数",
(SELECT temp1.NATION_NAME FROM temp1 where temp1.PF_CODE = p.PF_CODE AND ind = 2) AS "2位 国名",
(SELECT temp1.am FROM temp1 where temp1.PF_CODE = p.PF_CODE AND ind = 2) AS "2位 人数",
(SELECT temp1.NATION_NAME FROM temp1 where temp1.PF_CODE = p.PF_CODE AND ind = 3) AS "3位 国名",
(SELECT temp1.am FROM temp1 where temp1.PF_CODE = p.PF_CODE AND ind = 3) AS "3位 人数",
su AS "合計人数"
FROM PREFECTURE as p
JOIN temp2 ON p.PF_CODE = temp2.PF_CODE
order by su desc, 1 desc;