SELECT 
    PREFECTURE.PF_CODE AS 都道府県コード,
    PREFECTURE.PF_NAME AS 都道府県名,
    TOP1_INFO.NATION_NAME AS `1位 国名`,
    TOP1_INFO.AMT AS `1位 人数`,
    TOP2_INFO.NATION_NAME AS `2位 国名`,
    TOP2_INFO.AMT AS `2位 人数`,
    TOP3_INFO.NATION_NAME AS `3位 国名`,
    TOP3_INFO.AMT AS `3位 人数`,
    PREFECTURE_GROUP.sum_AMT AS 合計人数
FROM PREFECTURE
LEFT JOIN (
    SELECT 
       PF_CODE,
       SUM(AMT) AS sum_AMT
    FROM FOREIGNER
    WHERE NATION_CODE != '113'
    GROUP BY PF_CODE
) PREFECTURE_GROUP ON PREFECTURE.PF_CODE = PREFECTURE_GROUP.PF_CODE
LEFT JOIN(
    SELECT 
        PF_CODE, 
        NATION_NAME, 
        AMT
    FROM 
    (
        SELECT 
            FOREIGNER.PF_CODE, 
            NATIONALITY.NATION_NAME, 
            FOREIGNER.AMT,
            ROW_NUMBER() OVER(PARTITION BY FOREIGNER.PF_CODE  ORDER BY FOREIGNER.AMT DESC,FOREIGNER.NATION_CODE ASC) AS amt_rank 
        FROM FOREIGNER
        LEFT JOIN NATIONALITY ON NATIONALITY.NATION_CODE = FOREIGNER.NATION_CODE
        WHERE FOREIGNER.NATION_CODE != '113'
    )
    WHERE amt_rank = 1
) TOP1_INFO ON TOP1_INFO.PF_CODE = PREFECTURE.PF_CODE
LEFT JOIN(
    SELECT 
        PF_CODE, 
        NATION_NAME, 
        AMT
    FROM 
    (
        SELECT 
            FOREIGNER.PF_CODE, 
            NATIONALITY.NATION_NAME, 
            FOREIGNER.AMT,
            ROW_NUMBER() OVER(PARTITION BY FOREIGNER.PF_CODE  ORDER BY FOREIGNER.AMT DESC,FOREIGNER.NATION_CODE ASC) AS amt_rank 
        FROM FOREIGNER
        LEFT JOIN NATIONALITY ON NATIONALITY.NATION_CODE = FOREIGNER.NATION_CODE
        WHERE FOREIGNER.NATION_CODE != '113'
    )
    WHERE amt_rank = 2
) TOP2_INFO ON TOP2_INFO.PF_CODE = PREFECTURE.PF_CODE
LEFT JOIN(
    SELECT 
        PF_CODE, 
        NATION_NAME, 
        AMT
    FROM 
    (
        SELECT 
            FOREIGNER.PF_CODE, 
            NATIONALITY.NATION_NAME, 
            FOREIGNER.AMT,
            ROW_NUMBER() OVER(PARTITION BY FOREIGNER.PF_CODE  ORDER BY FOREIGNER.AMT DESC,FOREIGNER.NATION_CODE ASC) AS amt_rank 
        FROM FOREIGNER
        LEFT JOIN NATIONALITY ON NATIONALITY.NATION_CODE = FOREIGNER.NATION_CODE
        WHERE FOREIGNER.NATION_CODE != '113'
    )
    WHERE amt_rank = 3
) TOP3_INFO ON TOP3_INFO.PF_CODE = PREFECTURE.PF_CODE
WHERE TOP1_INFO.PF_CODE IS NOT NULL AND TOP2_INFO.PF_CODE IS NOT NULL AND TOP3_INFO.PF_CODE IS NOT NULL
GROUP BY PREFECTURE.PF_CODE
ORDER BY 
    PREFECTURE_GROUP.sum_AMT DESC,
    PREFECTURE.PF_CODE