SELECT
    PREFECTURE.PF_CODE AS `都道府県コード`,
    PREFECTURE.PF_NAME AS `都道府県名`,
    NATIONALITY_1.NATION_NAME AS `1位 国名`,
    FOREIGNER_1.AMT AS `1位 人数`,
    NATIONALITY_2.NATION_NAME AS `2位 国名`,
    FOREIGNER_2.AMT AS `2位 人数`,
    NATIONALITY_3.NATION_NAME AS `3位 国名`,
    FOREIGNER_3.AMT AS `3位 人数`,
    `合計人数`
FROM
(
    SELECT
        PREFECTURE.PF_CODE,
        PREFECTURE.PF_NAME,
        (
            SELECT
                FOREIGNER.NATION_CODE
            FROM
                FOREIGNER
            WHERE
                FOREIGNER.PF_CODE = PREFECTURE.PF_CODE AND
                FOREIGNER.NATION_CODE != 113
            ORDER BY 
                AMT DESC,
                FOREIGNER.NATION_CODE
            LIMIT 1 OFFSET 0
        ) AS `1位の国籍コード`,
        (
            SELECT
                FOREIGNER.NATION_CODE
            FROM
                FOREIGNER
            WHERE
                FOREIGNER.PF_CODE = PREFECTURE.PF_CODE AND
                FOREIGNER.NATION_CODE != 113
            ORDER BY 
                AMT DESC,
                FOREIGNER.NATION_CODE
            LIMIT 1 OFFSET 1
        ) AS `2位の国籍コード`,
        (
            SELECT
                FOREIGNER.NATION_CODE
            FROM
                FOREIGNER
            WHERE
                FOREIGNER.PF_CODE = PREFECTURE.PF_CODE AND
                FOREIGNER.NATION_CODE != 113
            ORDER BY 
                AMT DESC,
                FOREIGNER.NATION_CODE
            LIMIT 1 OFFSET 2
        ) AS `3位の国籍コード`,
        (
            SELECT
                SUM(FOREIGNER.AMT)
            FROM
                FOREIGNER
            WHERE
                FOREIGNER.PF_CODE = PREFECTURE.PF_CODE AND
                FOREIGNER.NATION_CODE != 113
        ) AS `合計人数`
    FROM
        PREFECTURE
) AS
    PREFECTURE
INNER JOIN
    NATIONALITY AS
    NATIONALITY_1 ON
    NATIONALITY_1.NATION_CODE = `1位の国籍コード`
INNER JOIN
    FOREIGNER AS
    FOREIGNER_1 ON
    FOREIGNER_1.PF_CODE = PREFECTURE.PF_CODE AND
    FOREIGNER_1.NATION_CODE = `1位の国籍コード`
INNER JOIN
    NATIONALITY AS
    NATIONALITY_2 ON
    NATIONALITY_2.NATION_CODE = `2位の国籍コード`
INNER JOIN
    FOREIGNER AS
    FOREIGNER_2 ON
    FOREIGNER_2.PF_CODE = PREFECTURE.PF_CODE AND
    FOREIGNER_2.NATION_CODE = `2位の国籍コード`
INNER JOIN
    NATIONALITY AS
    NATIONALITY_3 ON
    NATIONALITY_3.NATION_CODE = `3位の国籍コード`
INNER JOIN
    FOREIGNER AS
    FOREIGNER_3 ON
    FOREIGNER_3.PF_CODE = PREFECTURE.PF_CODE AND
    FOREIGNER_3.NATION_CODE = `3位の国籍コード`
ORDER BY
    `合計人数` DESC,
    `都道府県コード`