WITH TOTAL AS
(
    SELECT
        F.PF_CODE	    AS 都道府県コード
    ,	P.PF_NAME	    AS 都道府県名
    ,   N.NATION_NAME   AS 国名
    ,   SUM(F.AMT)      AS 合計人数
    ,   RANK() OVER(PARTITION BY F.PF_CODE ORDER BY SUM(F.AMT) DESC, F.NATION_CODE ASC) AS 順位
    FROM
        FOREIGNER F
        INNER JOIN PREFECTURE P
            ON F.PF_CODE = P.PF_CODE
        INNER JOIN NATIONALITY N
            ON F.NATION_CODE = N.NATION_CODE
    WHERE
        --国籍コード= '113'（その他）のデータは除いて集計すること
        F.NATION_CODE <> '113'
    GROUP BY
        F.PF_CODE
    ,	P.PF_NAME
    ,   N.NATION_NAME
)
SELECT
    T.都道府県コード
,   T.都道府県名
,   (SELECT 国名 FROM TOTAL WHERE TOTAL.都道府県コード = T.都道府県コード AND 順位 = 1)     AS "1位 国名"
,   (SELECT 合計人数 FROM TOTAL WHERE TOTAL.都道府県コード = T.都道府県コード AND 順位 = 1) AS "1位 人数"
,   (SELECT 国名 FROM TOTAL WHERE TOTAL.都道府県コード = T.都道府県コード AND 順位 = 2)     AS "2位 国名"
,   (SELECT 合計人数 FROM TOTAL WHERE TOTAL.都道府県コード = T.都道府県コード AND 順位 = 2) AS "2位 人数"
,   (SELECT 国名 FROM TOTAL WHERE TOTAL.都道府県コード = T.都道府県コード AND 順位 = 3)     AS "3位 国名"
,   (SELECT 合計人数 FROM TOTAL WHERE TOTAL.都道府県コード = T.都道府県コード AND 順位 = 3) AS "3位 人数"
,	SUM(合計人数)   AS "合計人数"
FROM 
    TOTAL T
GROUP BY
    T.都道府県コード
,   T.都道府県名
ORDER BY
    合計人数 DESC
,   T.都道府県コード ASC