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