SELECT 
        PF_CODE as '都道府県コード'
        ,PF_NAME as '都道府県名'
        ,max(CASE WHEN rnk = 1 THEN NATION_NAME END) as '1位 国名'
        ,sum(CASE WHEN rnk = 1 THEN AMT ELSE 0 END) as '1位 人数'
        ,max(CASE WHEN rnk = 2 THEN NATION_NAME END) as '2位 国名'
        ,sum(CASE WHEN rnk = 2 THEN AMT ELSE 0 END) as '2位 人数'
        ,max(CASE WHEN rnk = 3 THEN NATION_NAME END) as '3位 国名'
        ,sum(CASE WHEN rnk = 3 THEN AMT ELSE 0 END) as '3位 人数'
        ,sum(AMT) as '合計人数'
    FROM (
        SELECT
            f.PF_CODE
            ,p.PF_NAME
            ,f.NATION_CODE
            ,n.NATION_NAME
            ,AMT
            ,RANK() OVER (
                PARTITION BY f.PF_CODE
                ORDER BY AMT DESC , f.NATION_CODE ASC
                ) as rnk
        FROM 
            FOREIGNER as f
        INNER JOIN PREFECTURE as p ON f.PF_CODE = p.PF_CODE
        INNER JOIN NATIONALITY as n ON f.NATION_CODE = n.NATION_CODE
        WHERE 
            f.NATION_CODE <> '113'
        )
    GROUP BY 
        PF_CODE
        ,PF_NAME
    ORDER BY 
        合計人数 desc 
        ,PF_CODE
;