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