SELECT
    P.PF_CODE AS 都道府県コード,
    P.PF_NAME AS 都道府県,
    F1.NATION_NAME AS `1位 国名`,
    F1.AMT AS `1位 人数`,
    F2.NATION_NAME AS `2位 国名`,
    F2.AMT AS `2位 人数`,
    F3.NATION_NAME AS `3位 国名`,
    F3.AMT AS `3位 人数`,
    F_ALL.all_AMT AS `合計人数`
FROM PREFECTURE AS P
LEFT JOIN
(SELECT 
    PF_CODE, 
    NATION_NAME, 
    AMT
FROM 
    (SELECT 
        F.PF_CODE, 
        N.NATION_NAME, 
        F.AMT,
        ROW_NUMBER() OVER(PARTITION BY F.PF_CODE  ORDER BY F.AMT DESC,F.NATION_CODE ASC) AS rank_result 
    FROM FOREIGNER AS F
    LEFT JOIN NATIONALITY AS N ON N.NATION_CODE = F.NATION_CODE
    WHERE F.NATION_CODE != '113'
    ) AS F
WHERE rank_result =1
) F1 ON P.PF_CODE = F1.PF_CODE
LEFT JOIN(
    SELECT 
    PF_CODE, 
    NATION_NAME, 
    AMT
FROM 
    (SELECT 
        F.PF_CODE, 
        N.NATION_NAME, 
        F.AMT,
        ROW_NUMBER() OVER(PARTITION BY F.PF_CODE  ORDER BY F.AMT DESC,F.NATION_CODE ASC) AS rank_result 
    FROM FOREIGNER AS F
    LEFT JOIN NATIONALITY AS N ON N.NATION_CODE = F.NATION_CODE
    WHERE F.NATION_CODE != '113'
    ) AS F
WHERE rank_result =2
) F2 ON P.PF_CODE = F2.PF_CODE
LEFT JOIN(
    SELECT 
    PF_CODE, 
    NATION_NAME, 
    AMT
    FROM 
    (SELECT 
        F.PF_CODE, 
        N.NATION_NAME, 
        F.AMT,
        ROW_NUMBER() OVER(PARTITION BY F.PF_CODE  ORDER BY F.AMT DESC,F.NATION_CODE ASC) AS rank_result 
    FROM FOREIGNER AS F
    LEFT JOIN NATIONALITY AS N ON N.NATION_CODE = F.NATION_CODE
    WHERE F.NATION_CODE != '113'
    ) AS F
    WHERE rank_result =3
) F3 ON P.PF_CODE = F3.PF_CODE
LEFT JOIN(
    SELECT 
        PF_CODE, 
        SUM(AMT) AS all_AMT
    FROM FOREIGNER 
    WHERE NATION_CODE != '113'
    GROUP BY PF_CODE
) F_ALL ON P.PF_CODE = F_ALL.PF_CODE
WHERE F1.PF_CODE is not null  AND F1.PF_CODE is not null
ORDER BY
    F_ALL.all_AMT DESC,
    P.PF_CODE ASC