SELECT
     MAIN.PF_CODE AS '都道府県コード'
    ,MAIN.PF_NAME AS '都道府県名'
    ,MAX(NATION_NAME_1) AS '1位 国名'
    ,MAX(AMT_1) AS '1位 人数'
    ,MAX(NATION_NAME_2) AS '2位 国名'
    ,MAX(AMT_2) AS '2位 人数'
    ,MAX(NATION_NAME_3) AS '3位 国名'
    ,MAX(AMT_3) AS '3位 人数'
    ,KEI.AMT_KEI AS '合計人数'
FROM
(
    SELECT
         F.PF_CODE
        ,F.PF_NAME
        ,CASE WHEN F.RANK=1 THEN F.NATION_NAME END AS NATION_NAME_1
        ,CASE WHEN F.RANK=1 THEN F.AMT END AS AMT_1
        ,CASE WHEN F.RANK=2 THEN F.NATION_NAME END AS NATION_NAME_2
        ,CASE WHEN F.RANK=2 THEN F.AMT END AS AMT_2
        ,CASE WHEN F.RANK=3 THEN F.NATION_NAME END AS NATION_NAME_3
        ,CASE WHEN F.RANK=3 THEN F.AMT END AS AMT_3
        
    FROM
    (
        SELECT
             ROW_NUMBER() OVER (PARTITION BY F.PF_CODE ORDER BY F.AMT DESC) AS RANK
            ,F.PF_CODE
            ,P.PF_NAME
            ,F.NATION_CODE
            ,N.NATION_NAME
            ,F.AMT
        FROM
            FOREIGNER AS F
        INNER JOIN
            NATIONALITY AS N
        ON
            N.NATION_CODE = F.NATION_CODE
        INNER JOIN
            PREFECTURE AS P
        ON
            P.PF_CODE = F.PF_CODE
        WHERE
            F.NATION_CODE != '113'
    ) AS F
    
    WHERE
        F.RANK <= 3
        
) AS MAIN

INNER JOIN
(
    SELECT
         PF_CODE
        ,SUM(AMT) AS AMT_KEI
    FROM
        FOREIGNER
    WHERE
        NATION_CODE != '113'
    GROUP BY
        PF_CODE
) AS KEI
ON
    KEI.PF_CODE = MAIN.PF_CODE

GROUP BY
    MAIN.PF_CODE

ORDER BY
     KEI.AMT_KEI DESC
    ,MAIN.PF_CODE ASC