SELECT
PF_CODE AS 都道府県コード,
PF_NAME AS 都道府県名,
MAX(CASE WHEN RANK = 1 THEN NATION_NAME END) AS '1位 国名',
MAX(CASE WHEN RANK = 1 THEN AMT END) AS '1位 人数',
MAX(CASE WHEN RANK = 2 THEN NATION_NAME END) AS '2位 国名',
MAX(CASE WHEN RANK = 2 THEN AMT END) AS '2位 人数',
MAX(CASE WHEN RANK = 3 THEN NATION_NAME END) AS '3位 国名',
MAX(CASE WHEN RANK = 3 THEN AMT END) AS '3位 人数',
SUM(AMT) AS 合計人数
FROM(
        SELECT
            F.PF_CODE AS PF_CODE
            , P.PF_NAME AS PF_NAME
            , N.NATION_NAME AS NATION_NAME
            , F.AMT AS AMT
            , RANK() OVER ( 
                PARTITION BY
                    F.PF_CODE 
                ORDER BY
                    AMT DESC
                    , F.NATION_CODE
            ) AS RANK                           
        FROM
            FOREIGNER AS F 
            INNER JOIN NATIONALITY AS N 
                ON N.NATION_CODE = F.NATION_CODE 
            INNER JOIN PREFECTURE AS P 
                ON P.PF_CODE = F.PF_CODE 
        WHERE
            F.NATION_CODE != '113'
    ) RANKING
    GROUP BY PF_CODE
    ORDER BY 合計人数 DESC,PF_CODE