with top3 as (
    select
      PF_CODE,
      NATION_CODE,
      AMT,
      ROW_NUMBER() OVER(partition by PF_CODE order by AMT desc, NATION_CODE) as id
    from
        FOREIGNER
    where NATION_CODE <> '113'
)
select
    f.PF_CODE as 都道府県コード,
    p.PF_NAME as 都道府県名,
    max(case f.id when 1 then n.NATION_NAME else null end) as '1位 国名',
    max(case f.id when 1 then f.AMT else null end) as '1位 人数',
    max(case f.id when 2 then n.NATION_NAME else null end) as '2位 国名',
    max(case f.id when 2 then f.AMT else null end) as '2位 人数',
    max(case f.id when 3 then n.NATION_NAME else null end) as '3位 国名',
    max(case f.id when 3 then f.AMT else null end) as '3位 人数',
    sum(f.AMT) as 合計人数
from
    top3 f
inner join
    NATIONALITY n
    on n.NATION_CODE = f.NATION_CODE
inner join
    PREFECTURE p
    on p.PF_CODE = f.PF_CODE
where f.NATION_CODE <> '113'
group by
    f.PF_CODE
order by
    合計人数 desc,
    f.PF_CODE;