SELECT PR.PF_CODE AS '都道府県コード'
,PR.PF_NAME AS '都道府県名'
,NO3.NA1 AS '1位 国名'
,NO3.AMT1 AS '1位 人数'
,NO3.NA2 AS '2位 国名'
,NO3.AMT2 AS '2位 人数'
,NO3.NA3 AS '3位 国名'
,NO3.AMT3 AS '3位 人数'
,SUM(FO.AMT) AS '合計人数'
FROM PREFECTURE AS PR
INNER JOIN FOREIGNER AS FO
ON PR.PF_CODE = FO.PF_CODE
INNER JOIN
(
    SELECT PR3.PF_CODE
        ,PR3.PF_NAME
        ,NA3.NATION_CODE
        ,NA3.NATION_NAME AS 'NA3'
        ,MAX(AMT) AS 'AMT3'
        ,NO2.NA2
        ,NO2.AMT2
        ,NO2.NA1
        ,NO2.AMT1
    FROM FOREIGNER AS FO3
    LEFT OUTER JOIN PREFECTURE AS PR3
    ON PR3.PF_CODE = FO3.PF_CODE
    INNER JOIN NATIONALITY AS NA3
    ON FO3.NATION_CODE = NA3.NATION_CODE
    INNER JOIN (
        SELECT PR2.PF_CODE
            ,PR2.PF_NAME
            ,NO1.NATION_CODE AS 'NAC1'
            ,NA2.NATION_CODE AS 'NAC2'
            ,NA2.NATION_NAME AS 'NA2'
            ,MAX(AMT) AS 'AMT2'
            ,NO1.NA1
            ,NO1.AMT1
        FROM FOREIGNER AS FO2
        LEFT OUTER JOIN PREFECTURE AS PR2
        ON PR2.PF_CODE = FO2.PF_CODE
        INNER JOIN NATIONALITY AS NA2
        ON FO2.NATION_CODE = NA2.NATION_CODE
        INNER JOIN (
            SELECT PR1.PF_CODE
                ,PR1.PF_NAME
                ,NA1.NATION_CODE
                ,NA1.NATION_NAME AS 'NA1'
                ,MAX(AMT) AS 'AMT1'
            FROM FOREIGNER AS FO1
            LEFT OUTER JOIN PREFECTURE AS PR1
            ON PR1.PF_CODE = FO1.PF_CODE
            INNER JOIN NATIONALITY AS NA1
            ON FO1.NATION_CODE = NA1.NATION_CODE
            WHERE NA1.NATION_CODE <> '113'
            GROUP BY PR1.PF_CODE,PR1.PF_NAME
        ) AS NO1
        ON PR2.PF_CODE = NO1.PF_CODE
        WHERE NA2.NATION_CODE <> '113'
        AND NA2.NATION_CODE <> NO1.NATION_CODE
        GROUP BY PR2.PF_CODE,PR2.PF_NAME
    ) AS NO2
    ON PR3.PF_CODE = NO2.PF_CODE
    WHERE NA3.NATION_CODE <> '113'
    AND NA3.NATION_CODE <> NO2.NAC1
    AND NA3.NATION_CODE <> NO2.NAC2
    GROUP BY PR3.PF_CODE,PR3.PF_NAME
) AS NO3
ON PR.PF_CODE = NO3.PF_CODE
WHERE FO.NATION_CODE <> '113'
GROUP BY PR.PF_CODE
,PR.PF_NAME
,NO3.NA1
,NO3.AMT1
,NO3.NA2
,NO3.AMT2
,NO3.NA3
,NO3.AMT3
ORDER BY  SUM(FO.AMT) DESC
,PR.PF_CODE