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