SELECT
    F.PF_CODE AS 都道府県コード,
    PF_NAME AS 都道府県名,
    MAX(CASE WHEN F.AMT_RANK = 1 THEN N.NATION_NAME END) AS "1位 国名",
    SUM(CASE WHEN F.AMT_RANK = 1 THEN F.AMT END) AS "1位 人数",
    MAX(CASE WHEN F.AMT_RANK = 2 THEN N.NATION_NAME END) AS "2位 国名",
    SUM(CASE WHEN F.AMT_RANK = 2 THEN F.AMT END) AS "2位 人数",
    MAX(CASE WHEN F.AMT_RANK = 3 THEN N.NATION_NAME END) AS "3位 国名",
    SUM(CASE WHEN F.AMT_RANK = 3 THEN F.AMT END) AS "3位 人数",
    SUM(F.AMT) AS 合計人数
FROM
    (
        SELECT
            NATION_CODE,
            PF_CODE,
            AMT,
            RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, NATION_CODE) AS AMT_RANK
        FROM
            FOREIGNER
        WHERE
            NATION_CODE != 113
    ) AS F
LEFT JOIN
    NATIONALITY AS N ON N.NATION_CODE = F.NATION_CODE
LEFT JOIN
    PREFECTURE AS P ON P.PF_CODE = F.PF_CODE
GROUP BY
    F.PF_CODE, PF_NAME
ORDER BY
    合計人数 DESC, F.PF_CODE
;