WITH SUB AS (
    SELECT
        RANK() OVER (PARTITION BY FOREIGNER.PF_CODE ORDER BY AMT DESC , FOREIGNER.NATION_CODE ) AS RANK
        ,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
)

--メイン処理
SELECT
    SUB.PF_CODE AS 都道府県コード
    , SUB.PF_NAME AS 都道府県名
    , MAX(CASE SUB.RANK WHEN 1 THEN NATION_NAME END) AS "1位 国名"
    , MAX(CASE SUB.RANK WHEN 1 THEN AMT END) AS "1位 人数"
    , MAX(CASE SUB.RANK WHEN 2 THEN NATION_NAME END) AS "2位 国名"
    , MAX(CASE SUB.RANK WHEN 2 THEN AMT END) AS "2位 人数"
    , MAX(CASE SUB.RANK WHEN 3 THEN NATION_NAME END) AS "3位 国名"
    , MAX(CASE SUB.RANK WHEN 3 THEN AMT END) AS "3位 人数"
    , SUM(SUB.AMT) AS 合計人数 
FROM SUB
GROUP BY SUB.PF_CODE
ORDER BY 合計人数 DESC, 都道府県コード ASC