SELECT
    FIRST_PF_CODE AS 都道府県コード
    ,FIRST_PF_NAME AS 都道府県名
    ,FIRST_NATION_NAME AS '1位 国名'
    ,FIRST_PF_AMT AS '1位 人数'
    ,SECOND_NATION_NAME AS '2位 国名'
    ,SECOND_PF_AMT AS '2位 人数'
    ,NA3.NATION_NAME AS '3位 国名'
    ,MAX(FO3.AMT) AS '3位 人数'
    ,SUM(FT.AMT) AS 合計
FROM
    (SELECT
        FN.FIRST_PF_CODE
        ,FN.FIRST_PF_NAME
        ,FN.FIRST_NATION_CODE
        ,FN.FIRST_NATION_NAME
        ,FN.FIRST_PF_AMT
        ,NA2.NATION_CODE AS SECOND_NATION_CODE
        ,NA2.NATION_NAME AS SECOND_NATION_NAME
        ,MAX(FO2.AMT) AS SECOND_PF_AMT
    FROM
        (SELECT
            PR1.PF_CODE AS FIRST_PF_CODE
            ,PR1.PF_NAME AS FIRST_PF_NAME
            ,NA1.NATION_NAME AS FIRST_NATION_NAME
            ,NA1.NATION_CODE AS FIRST_NATION_CODE
            ,MAX(FO1.AMT) AS FIRST_PF_AMT
        FROM
            PREFECTURE AS PR1
            INNER JOIN FOREIGNER AS FO1
                ON FO1.PF_CODE = PR1.PF_CODE
            INNER JOIN NATIONALITY AS NA1
                ON NA1.NATION_CODE = FO1.NATION_CODE
        WHERE
            FO1.NATION_CODE <> '113'
        GROUP BY
            PR1.PF_CODE) AS FN
        INNER JOIN FOREIGNER AS FO2
            ON FO2.PF_CODE = FN.FIRST_PF_CODE
        INNER JOIN NATIONALITY AS NA2
            ON NA2.NATION_CODE = FO2.NATION_CODE
    WHERE
        FO2.NATION_CODE <> '113' AND
        FO2.NATION_CODE <> FN.FIRST_NATION_CODE
    GROUP BY
        FN.FIRST_PF_CODE
        ,FN.FIRST_PF_NAME
        ,FN.FIRST_NATION_NAME
        ,FN.FIRST_PF_AMT) AS SN
    INNER JOIN FOREIGNER AS FO3
        ON FO3.PF_CODE = SN.FIRST_PF_CODE
    INNER JOIN NATIONALITY AS NA3
        ON NA3.NATION_CODE = FO3.NATION_CODE
    INNER JOIN FOREIGNER AS FT
        ON FT.PF_CODE = SN.FIRST_PF_CODE
WHERE
    FO3.NATION_CODE <> '113' AND
    FO3.NATION_CODE <> SN.FIRST_NATION_CODE AND
    FO3.NATION_CODE <> SN.SECOND_NATION_CODE AND
    FT.NATION_CODE <> '113'
GROUP BY
    FIRST_PF_CODE
    ,FIRST_PF_NAME
    ,FIRST_NATION_NAME
    ,FIRST_PF_AMT
    ,SECOND_NATION_NAME
    ,SECOND_PF_AMT
ORDER BY
    合計 DESC
    ,FIRST_PF_CODE ASC