SELECT
    PF_CODE AS 都道府県コード,
    PF_NAME AS 都道府県名,
    (SELECT NATION_NAME FROM FOREIGNER JOIN NATIONALITY USING (NATION_CODE) WHERE P.PF_CODE = PF_CODE AND NATION_CODE <> 113 ORDER BY AMT DESC LIMIT 1) AS '1位 国名',
    (SELECT AMT FROM FOREIGNER WHERE P.PF_CODE = PF_CODE AND NATION_CODE <> 113 ORDER BY AMT DESC LIMIT 1) AS '1位 人数',
    (SELECT NATION_NAME FROM FOREIGNER JOIN NATIONALITY USING (NATION_CODE) WHERE P.PF_CODE = PF_CODE AND NATION_CODE <> 113 ORDER BY AMT DESC LIMIT 1 OFFSET 1) AS '2位 国名',
    (SELECT AMT FROM FOREIGNER WHERE P.PF_CODE = PF_CODE AND NATION_CODE <> 113 ORDER BY AMT DESC LIMIT 1 OFFSET 1) AS '2位 人数',
    (SELECT NATION_NAME FROM FOREIGNER JOIN NATIONALITY USING (NATION_CODE) WHERE P.PF_CODE = PF_CODE AND NATION_CODE <> 113 ORDER BY AMT DESC LIMIT 1 OFFSET 2) AS '3位 国名',
    (SELECT AMT FROM FOREIGNER WHERE P.PF_CODE = PF_CODE AND NATION_CODE <> 113 ORDER BY AMT DESC LIMIT 1 OFFSET 2) AS '3位 人数',
    SUM(AMT) AS '合計人数'
FROM PREFECTURE P JOIN FOREIGNER USING (PF_CODE)
WHERE NATION_CODE <> 113
GROUP BY PF_CODE
ORDER BY 9 DESC, 1;