select
    a.[PF_CODE] as [都道府県コード]
    ,a.[PF_NAME] as [都道府県名]
    ,a.[NATION_NAME] as [1位 国名]
    ,a.[sum_amt] as [1位 人数]
    ,b.[NATION_NAME] as [2位 国名]
    ,b.[sum_amt] as [2位 人数]
    ,c.[NATION_NAME] as [3位 国名]
    ,c.[sum_amt] as [3位 人数]
    ,d.[sum_amt] as [合計人数]
from (
select
	f.PF_CODE as [PF_CODE]
	,p.PF_NAME as [PF_NAME]
	,f.NATION_CODE as [NATION_CODE]
	,n.NATION_NAME as [NATION_NAME]
    ,rank() over(partition by f.PF_CODE,p.PF_NAME order by sum(f.amt) desc) as [RANK]
	,sum(f.amt) as [sum_amt]
from FOREIGNER as f
left join NATIONALITY as n
on f.NATION_CODE = n.NATION_CODE
left join PREFECTURE as p
on f.PF_CODE = p.PF_CODE
where f.NATION_CODE <> '113'
group by
	f.PF_CODE
	,p.PF_NAME
	,f.NATION_CODE
	,n.NATION_NAME
) as a
left join
(
select
	f.PF_CODE as [PF_CODE]
	,p.PF_NAME as [PF_NAME]
	,f.NATION_CODE as [NATION_CODE]
	,n.NATION_NAME as [NATION_NAME]
    ,rank() over(partition by f.PF_CODE,p.PF_NAME order by sum(f.amt) desc) as [RANK]
	,sum(f.amt) as [sum_amt]
from FOREIGNER as f
left join NATIONALITY as n
on f.NATION_CODE = n.NATION_CODE
left join PREFECTURE as p
on f.PF_CODE = p.PF_CODE
where f.NATION_CODE <> '113'
group by
	f.PF_CODE
	,p.PF_NAME
	,f.NATION_CODE
	,n.NATION_NAME
) as b
on a.PF_CODE = b.PF_CODE
and a.PF_NAME = b.PF_NAME
and b.RANK = 2
left join
(
select
	f.PF_CODE as [PF_CODE]
	,p.PF_NAME as [PF_NAME]
	,f.NATION_CODE as [NATION_CODE]
	,n.NATION_NAME as [NATION_NAME]
    ,rank() over(partition by f.PF_CODE,p.PF_NAME order by sum(f.amt) desc) as [RANK]
	,sum(f.amt) as [sum_amt]
from FOREIGNER as f
left join NATIONALITY as n
on f.NATION_CODE = n.NATION_CODE
left join PREFECTURE as p
on f.PF_CODE = p.PF_CODE
where f.NATION_CODE <> '113'
group by
	f.PF_CODE
	,p.PF_NAME
	,f.NATION_CODE
	,n.NATION_NAME
) as c
on a.PF_CODE = c.PF_CODE
and a.PF_NAME = c.PF_NAME
and c.RANK = 3
left join
(
select
	f.PF_CODE as [PF_CODE]
	,p.PF_NAME as [PF_NAME]
	,sum(f.amt) as [sum_amt]
from FOREIGNER as f
left join NATIONALITY as n
on f.NATION_CODE = n.NATION_CODE
left join PREFECTURE as p
on f.PF_CODE = p.PF_CODE
where f.NATION_CODE <> '113'
group by
	f.PF_CODE
	,p.PF_NAME
) as d
on a.PF_CODE = d.PF_CODE
and a.PF_NAME = d.PF_NAME
where a.[RANK] = 1
group by
	a.[PF_CODE]
    ,a.[PF_NAME]
order by 9 desc,1 asc