WITH TMP_TABLE AS(
    SELECT
        FOREIGNER.PF_CODE AS 都道府県コード,
        PREFECTURE.PF_NAME AS 都道府県名,
        NATIONALITY.NATION_NAME AS 国名,
        RANK() OVER(
            PARTITION BY
                FOREIGNER.PF_CODE
            ORDER BY
                AMT DESC, 
                FOREIGNER.NATION_CODE ASC
        ) AS RNK,
        AMT
    FROM
        FOREIGNER
        INNER JOIN
            PREFECTURE ON PREFECTURE.PF_CODE = FOREIGNER.PF_CODE,
            NATIONALITY ON NATIONALITY.NATION_CODE = FOREIGNER.NATION_CODE
    WHERE
        FOREIGNER.NATION_CODE != '113'
    GROUP BY
        FOREIGNER.PF_CODE,
        FOREIGNER.NATION_CODE
)
SELECT
    都道府県コード,
    都道府県名,
    MAX(
        CASE RNK
            WHEN 1 THEN 国名
            ELSE ''
        END
    ) AS '1位 国名',
    MAX(
        CASE RNK
            WHEN 1 THEN AMT
            ELSE -1
        END
    ) AS '1位 人数',
    MAX(
        CASE RNK
            WHEN 2 THEN 国名
            ELSE ''
        END
    ) AS '2位 国名',
    MAX(
        CASE RNK
            WHEN 2 THEN AMT
            ELSE -1
        END
    ) AS '2位 人数',
    MAX(
        CASE RNK
            WHEN 3 THEN 国名
            ELSE ''
        END
    ) AS '3位 国名',
    MAX(
        CASE RNK
            WHEN 3 THEN AMT
            ELSE -1
        END
    ) AS '3位 人数',
    SUM(AMT) AS 合計人数
FROM
    TMP_TABLE
GROUP BY
    都道府県コード
ORDER BY
    合計人数 DESC,
    都道府県コード ASC