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