SELECT
    pref.PF_CODE as 都道府県コード
    ,pref.PF_NAME as 都道府県名
    ,nation1.NATION_NAME as '1位 国名'
    ,"1位 人数"
    ,nation2.NATION_NAME as '2位 国名'
    ,"2位 人数"
    ,nation3.NATION_NAME as '3位 国名'
    ,"3位 人数"
    ,合計人数
FROM
(
    SELECT
        PF_CODE
        ,SUM(CASE WHEN rank = 1 THEN NATION_CODE ELSE 0 END ) as '1位 国コード'
        ,SUM(CASE WHEN rank = 1 THEN AMT ELSE 0 END         ) as '1位 人数'
        ,SUM(CASE WHEN rank = 2 THEN NATION_CODE ELSE 0 END ) as '2位 国コード'
        ,SUM(CASE WHEN rank = 2 THEN AMT ELSE 0 END         ) as '2位 人数'
        ,SUM(CASE WHEN rank = 3 THEN NATION_CODE ELSE 0 END ) as '3位 国コード'
        ,SUM(CASE WHEN rank = 3 THEN AMT ELSE 0 END         ) as '3位 人数'
        ,合計人数
    FROM
        (
            SELECT
                PF_CODE
                ,NATION_CODE
                ,AMT
                ,RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC) as rank
                ,SUM(AMT) OVER(PARTITION BY PF_CODE) as 合計人数
            FROM
                FOREIGNER
            WHERE
                NATION_CODE != 113  -- 国籍：その他を除外
        ) as rank_data
    WHERE
        rank <= 3  -- 3位まで
    GROUP BY
        PF_CODE
) as best3_amt_by_pref
LEFT JOIN
    NATIONALITY nation1 ON nation1.NATION_CODE = best3_amt_by_pref.'1位 国コード'
LEFT JOIN
    NATIONALITY nation2 ON nation2.NATION_CODE = best3_amt_by_pref.'2位 国コード'
LEFT JOIN
    NATIONALITY nation3 ON nation3.NATION_CODE = best3_amt_by_pref.'3位 国コード'
LEFT JOIN
    PREFECTURE pref ON pref.PF_CODE = best3_amt_by_pref.PF_CODE
ORDER BY
    合計人数 DESC, 都道府県コード
;