WITH tmp AS (
SELECT
    FR.PF_CODE
    ,FR.NATION_CODE
    ,NY.NATION_NAME
    ,PF.PF_NAME
    ,RANK() OVER(PARTITION BY FR.PF_CODE ORDER BY FR.AMT DESC ,NY.NATION_CODE ASC) AS rn
    ,FR.AMT
FROM FOREIGNER AS FR
LEFT JOIN NATIONALITY AS NY
    ON FR.NATION_CODE = NY.NATION_CODE
LEFT JOIN PREFECTURE AS PF
    ON FR.PF_CODE = PF.PF_CODE
WHERE 
    FR.NATION_CODE <> 113
) ,rn1 AS (
select
    PF_CODE,PF_NAME,
    NATION_NAME AS '1位 国名',
    AMT AS '1位 人数'
from
    tmp
where
    rn = 1
),rn2 AS (
select
    PF_CODE,PF_NAME,
    NATION_NAME AS '2位 国名',
    AMT AS '2位 人数'
from
    tmp
where
    rn = 2
),rn3 AS (
select
    PF_CODE,PF_NAME,
    NATION_NAME AS '3位 国名',
    AMT AS '3位 人数'
from
    tmp
where
    rn = 3
), su AS(
SELECT
   PF_CODE
   ,SUM(AMT) AS 合計人数
FROM
    FOREIGNER
WHERE 
    NATION_CODE <> 113
GROUP BY 
    PF_CODE
)

SELECT
    rn1.PF_CODE AS 都道府県コード
    ,rn1.PF_NAME AS 都道府県
    ,rn1.'1位 国名'
    ,rn1.'1位 人数'
    ,rn2.'2位 国名'
    ,rn2.'2位 人数'
    ,rn3.'3位 国名'
    ,rn3.'3位 人数'
    ,su.合計人数
FROM rn1
left join rn2 on rn1.PF_CODE = rn2.PF_CODE
left join rn3 on rn1.PF_CODE = rn3.PF_CODE
left join su on rn1.PF_CODE = su.PF_CODE
ORDER BY 合計人数 DESC , 都道府県コード ASC