SELECT
    pref.PF_CODE as 都道府県コード
    ,pref.PF_NAME as 都道府県名
    ,nation1.NATION_NAME as '1位 国名'
    ,人数1 as '1位 人数'
    ,nation2.NATION_NAME as '2位 国名'
    ,人数2 as '2位 人数'
    ,nation3.NATION_NAME as '3位 国名'
    ,人数3 as '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
            ,ROW_NUMBER() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC, PF_CODE) as rank
            -- 集計した人数が同数の場合は、国籍コードの昇順で順位付け
            -- RANK: 同率があった場合順位は同じになり、その次は順位を飛ばす(1,1,3,...)
            -- DENSE_RANK: 同率があった場合順位は同じになり、その次は順位を飛ばさない(1,1,2...)
            -- → ROW_NUMBER: 同率があっても同じ順位にはならず、順位をカウントする
            ,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, 都道府県コード
;