SELECT
    FR.PF_CODE AS "都道府県コード",
    PF.PF_NAME AS "都道府県名",
    MAX(CASE WHEN FR."順位" = 1 THEN NA.NATION_NAME ELSE NULL END) AS "1位 国名",
    MAX(CASE WHEN FR."順位" = 1 THEN FR.AMT ELSE NULL END) AS "1位 人数",
    MAX(CASE WHEN FR."順位" = 2 THEN NA.NATION_NAME ELSE NULL END) AS "2位 国名",
    MAX(CASE WHEN FR."順位" = 2 THEN FR.AMT ELSE NULL END) AS "2位 人数",
    MAX(CASE WHEN FR."順位" = 3 THEN NA.NATION_NAME ELSE NULL END) AS "3位 国名",
    MAX(CASE WHEN FR."順位" = 3 THEN FR.AMT ELSE NULL END) AS "3位 人数",
    MAX(FR_TOTAL.AMT) AS "合計人数"
FROM
    (SELECT PF_CODE, NATION_CODE, AMT, ROW_NUMBER() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC) AS "順位" FROM FOREIGNER WHERE NATION_CODE <> '113') FR
    INNER JOIN (SELECT PF_CODE, SUM(AMT) AS AMT FROM FOREIGNER WHERE NATION_CODE <> '113' GROUP BY PF_CODE) FR_TOTAL
        ON FR.PF_CODE = FR_TOTAL.PF_CODE
    INNER JOIN NATIONALITY NA
        ON FR.NATION_CODE = NA.NATION_CODE
    INNER JOIN PREFECTURE PF
        ON FR.PF_CODE = PF.PF_CODE
GROUP BY FR.PF_CODE, PF.PF_NAME
ORDER BY "合計人数" DESC, "都道府県コード"