SELECT
    FG.PF_CODE AS '都道府県コード'
    ,PF.PF_NAME AS ' 都道府県名'
    ,FGRANK1.NATION_NAME AS '1位 国名'
    ,FGRANK1.AMT AS '1位 人数'
    ,FGRANK2.NATION_NAME AS '2位 国名'
    ,FGRANK2.AMT AS '2位 人数'
    ,FGRANK3.NATION_NAME AS '3位 国名'
    ,FGRANK3.AMT AS '3位 人数'
    ,SUM(FG.AMT) AS '合計人数'
FROM
    FOREIGNER AS FG
    LEFT OUTER JOIN(
SELECT
    FG.PF_CODE
    ,RANK() OVER(PARTITION BY FG.PF_CODE ORDER BY FG.AMT DESC,FG.NATION_CODE) AS RANKING
    ,FG.NATION_CODE
    ,PC.NATION_NAME
    ,FG.AMT AS AMT
FROM
    FOREIGNER AS FG
    LEFT OUTER JOIN NATIONALITY AS PC ON (FG.NATION_CODE = PC.NATION_CODE)
WHERE 
    FG.NATION_CODE <> '113'
) AS FGRANK1 ON(FG.PF_CODE = FGRANK1.PF_CODE AND FGRANK1.RANKING = 1)
    LEFT OUTER JOIN(
SELECT
    FG.PF_CODE
    ,RANK() OVER(PARTITION BY FG.PF_CODE ORDER BY FG.AMT DESC ,FG.NATION_CODE) AS RANKING
    ,PC.NATION_NAME
    ,FG.AMT AS AMT
FROM
    FOREIGNER AS FG
    LEFT OUTER JOIN NATIONALITY AS PC ON (FG.NATION_CODE = PC.NATION_CODE)
WHERE 
    FG.NATION_CODE <> '113'
) AS FGRANK2 ON(FG.PF_CODE = FGRANK2.PF_CODE AND FGRANK2.RANKING = 2)
    LEFT OUTER JOIN(
SELECT
    FG.PF_CODE
    ,RANK() OVER(PARTITION BY FG.PF_CODE ORDER BY FG.AMT DESC,FG.NATION_CODE) AS RANKING
    ,PC.NATION_NAME
    ,FG.AMT AS AMT
FROM
    FOREIGNER AS FG
    LEFT OUTER JOIN NATIONALITY AS PC ON (FG.NATION_CODE = PC.NATION_CODE)
WHERE 
    FG.NATION_CODE <> '113'
) AS FGRANK3 ON(FG.PF_CODE = FGRANK3.PF_CODE AND FGRANK3.RANKING = 3)
left outer join PREFECTURE PF ON (FG.PF_CODE = PF.PF_CODE)
WHERE 
    FG.NATION_CODE <> '113'
GROUP BY 
    FG.PF_CODE
ORDER BY
    SUM(FG.AMT) DESC
    ,FG.PF_CODE