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 ,FR.NATION_CODE ASC) AS rn
    ,FR.AMT
FROM FOREIGNER AS FR
INNER JOIN NATIONALITY AS NY ON FR.NATION_CODE = NY.NATION_CODE
INNER 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
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
;