select
    PF_CODE
    , PF_NAME
    , MAX(IIF(RANK=1,NATION_NAME,'')) as "1位 国名"
    , MAX(IIF(RANK=1,AMT,0)) as "1位 人数"
    , MAX(IIF(RANK=2,NATION_NAME,'')) as "2位 国名"
    , MAX(IIF(RANK=2,AMT,0)) as "2位 人数"
    , MAX(IIF(RANK=3,NATION_NAME,'')) as "3位 国名"
    , MAX(IIF(RANK=3,AMT,0)) as "3位 人数"
    , MAX(IIF(RANK=1,TOTAL_AMT, 0)) as "合計"
from 
    (
    select
        FORE.PF_CODE
        , PRE.PF_NAME
        , FORE.NATION_CODE
        , NAT.NATION_NAME
        , AMT
        , RANK
        , TOT.TOTAL_AMT
    FROM
        (
        select
            PF_CODE
            ,NATION_CODE
            ,SUM(AMT) AMT
            ,RANK() OVER (PARTITION BY PF_CODE ORDER BY SUM(AMT) DESC) AS RANK
        from 
            FOREIGNER
        WHERE
            NATION_CODE <> '113'
        GROUP BY 
            PF_CODE, NATION_CODE
        ) FORE
        INNER JOIN PREFECTURE PRE ON FORE.PF_CODE = PRE.PF_CODE
        INNER JOIN NATIONALITY NAT ON FORE.NATION_CODE = NAT.NATION_CODE
        INNER JOIN (
            select
                PF_CODE
                , SUM(AMT) as TOTAL_AMT
            from 
                FOREIGNER
            WHERE
                NATION_CODE <> '113'
            GROUP BY 
                PF_CODE
        ) TOT ON FORE.PF_CODE = TOT.PF_CODE
    WHERE
        RANK < 4
    )
GROUP BY
    PF_CODE
ORDER BY
    TOTAL_AMT desc, PF_CODE