WITH BASE AS(
    SELECT 
    PF_CODE,
    PF_NAME,
    AMT,
    NATION_NAME,
    rank() OVER (PARTITION BY PF_NAME ORDER BY AMT DESC) as rank
    FROM FOREIGNER
    JOIN NATIONALITY USING(NATION_CODE)
    JOIN PREFECTURE USING(PF_CODE)
    WHERE NATION_CODE!=113
),
RANK1 as (
    SELECT 
    PF_CODE,
    PF_NAME,
    NATION_NAME,
    AMT
    FROM BASE WHERE rank=1
),
RANK2 as (
    SELECT 
    PF_CODE,
    PF_NAME,
    NATION_NAME,
    AMT
    FROM BASE WHERE rank=2
),
RANK3 as (
    SELECT 
    PF_CODE,
    PF_NAME,
    NATION_NAME,
    AMT
    FROM BASE WHERE rank=3
)
SELECT
    PF_CODE as '都道府県コード',
    PF_NAME as '都道府県名',
    RANK1.NATION_NAME as '1位 国名',
    RANK1.AMT as '1位 人数',
    RANK2.NATION_NAME as '2位 国名',
    RANK2.AMT as '2位 人数',
    RANK3.NATION_NAME as '3位 国名',
    RANK3.AMT as '3位 人数',
    RANK1.AMT + RANK2.AMT + RANK3.AMT as '合計人数'
FROM RANK1 
JOIN RANK2 USING(PF_CODE, PF_NAME)
JOIN RANK3 USING(PF_CODE, PF_NAME)
ORDER BY 合計人数 DESC, 都道府県コード ASC