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