select
	PF_CODE as 都道府県コード
	,PF_NAME as 都道府県名
	,max(case when rank_result = 1 then NATION_NAME else "" end) as "1位 国名"
	,max(case when rank_result = 1 then AMT else 0 end) as "1位 人数"
	,max(case when rank_result = 2 then NATION_NAME else "" end) as "2位 国名"
	,max(case when rank_result = 2 then AMT else 0 end) as "2位 人数"
	,max(case when rank_result = 3 then NATION_NAME else "" end) as "3位 国名"
	,max(case when rank_result = 3 then AMT else 0 end) as "3位 人数"
	,sum(AMT) as 合計人数
from
	(
	select 
	    t1.PF_CODE
	    ,PF_NAME
	    ,t1.NATION_CODE
	    ,NATION_NAME
	    ,AMT
	    ,rank() over(partition by t1.PF_CODE order by AMT desc,t1.NATION_CODE) as rank_result
	from 
	    FOREIGNER as t1
    inner join NATIONALITY as t2
    	on t1.NATION_CODE = t2.NATION_CODE
    inner join PREFECTURE as t3
    	on t1.PF_CODE = t3.PF_CODE
    where 
        t1.NATION_CODE != 130
	)
group by
    PF_CODE
order by
	合計人数 desc
	,都道府県コード