WITH COUNT_AMT AS (
    SELECT
        FOREIGNER.PF_CODE
        ,PREFECTURE.PF_NAME
        ,FOREIGNER.NATION_CODE
        ,NATIONALITY.NATION_NAME
        ,SUM(FOREIGNER.AMT) AS AMT
    FROM FOREIGNER
    INNER JOIN NATIONALITY ON FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
    INNER JOIN PREFECTURE ON FOREIGNER.PF_CODE = PREFECTURE.PF_CODE
    WHERE FOREIGNER.NATION_CODE <> "113"
    GROUP BY FOREIGNER.PF_CODE, FOREIGNER.NATION_CODE
    ORDER BY FOREIGNER.PF_CODE,AMT DESC,FOREIGNER.NATION_CODE
),
RANK AS (
    SELECT
        ROW_NUMBER() OVER(PARTITION BY COUNT_AMT.PF_CODE) AS NUM
        ,*
    FROM COUNT_AMT
),
ALL_SUM AS (
    SELECT
        COUNT_AMT.PF_CODE
        ,SUM(COUNT_AMT.AMT) AS ALL_AMT
    FROM COUNT_AMT
    GROUP BY COUNT_AMT.PF_CODE
)
--メイン処理
SELECT
    RANK.PF_CODE AS "都道府県コード"
    ,RANK.PF_NAME AS "都道府県名"
    ,CASE WHEN RANK.NUM = 1 THEN RANK.AMT ELSE "" END AS "1位 国名"
    ,CASE WHEN RANK.NUM = 1 THEN RANK.NATION_NAME ELSE "" END AS "1位 人数"
    ,CASE WHEN RANK.NUM = 2 THEN RANK.AMT ELSE "" END AS  "2位 国名"
    ,CASE WHEN RANK.NUM = 2 THEN RANK.NATION_NAME ELSE "" END AS "2位 人数"
    ,CASE WHEN RANK.NUM = 3 THEN RANK.AMT ELSE "" END AS  "3位 国名"
    ,CASE WHEN RANK.NUM = 3 THEN RANK.NATION_NAME ELSE "" END AS "3位 人数"
    ,ALL_SUM.ALL_AMT AS "合計人数"
FROM RANK
INNER JOIN ALL_SUM ON RANK.PF_CODE = ALL_SUM.PF_CODE
GROUP BY RANK.PF_CODE