SELECT
FO.PF_CODE AS 都道府県コード,
PR.PF_NAME AS 都道府県名,

FO1.NATION_NAME AS "1位 国名",
FO1.AMT AS "1位 人数",
FO2.NATION_NAME AS "2位 国名",
FO2.AMT AS "2位 人数",
FO3.NATION_NAME AS "3位 国名",
FO3.AMT AS "3位 人数",
SUM(FO.AMT) AS 合計人数

FROM
FOREIGNER AS FO

INNER JOIN FOREIGNER AS FO1
ON FO1.PF_CODE = FO.PF_CODE
AND FO1.NATION_CODE = FO.NATION_CODE

INNER JOIN FOREIGNER AS FO2
ON FO2.PF_CODE = FO.PF_CODE
AND FO2.NATION_CODE = FO.NATION_CODE

INNER JOIN FOREIGNER AS FO3
ON FO3.PF_CODE = FO.PF_CODE
AND FO3.NATION_CODE = FO.NATION_CODE

INNER JOIN PREFECTURE AS PR
ON PR.PF_CODE = FO.PF_CODE

INNER JOIN NATIONALITY AS NA
ON NA.NATION_CODE = FO,NATION_CODE


GROUP BY FO.PF_CODE,PR.PF_NAME

ORDER BY 
合計人数 DESC,
都道府県コード ASC;