select
MAIN.PF_CODE as 都道府県コード,
MAIN.PF_NAME as 都道府県名,
MAX(case when Rank=1 then MAIN.NATION_NAME end) as "1位 国名",
MAX(case when Rank=1 then MAIN.AMT end) as "1位 人数",
MAX(case when Rank=2 then MAIN.NATION_NAME end) as "2位 国名",
MAX(case when Rank=2 then MAIN.AMT end) as "2位 人数",
MAX(case when Rank=3 then MAIN.NATION_NAME end) as "3位 国名",
MAX(case when Rank=3 then MAIN.AMT end) as "3位 人数",
GOUKEI.AMT as "合計人数"
from(

select
PF_CODE,
PF_NAME,
NATION_NAME,
AMT,
RANK
from(

select
PREFECTURE.PF_CODE,
PREFECTURE.PF_NAME,
NATIONALITY.NATION_NAME,
SUM(FOREIGNER.AMT) as AMT,
rank() over(PARTITION BY PREFECTURE.PF_CODE,PREFECTURE.PF_NAME order by SUM(FOREIGNER.AMT) desc, NATIONALITY.NATION_CODE) as RANK
from FOREIGNER
inner join NATIONALITY
on FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
inner join PREFECTURE
on FOREIGNER.PF_CODE = PREFECTURE.PF_CODE
where NATIONALITY.NATION_NAME <> "その他"
group by PREFECTURE.PF_CODE, PREFECTURE.PF_NAME, NATIONALITY.NATION_NAME
)
where RANK <= 3
) as MAIN
inner join(
select
FOREIGNER.PF_CODE
,sum(FOREIGNER.AMT) AS AMT
from FOREIGNER
inner join NATIONALITY
on FOREIGNER.NATION_CODE = NATIONALITY.NATION_CODE
where NATIONALITY.NATION_NAME <> "その他"
group by FOREIGNER.PF_CODE
) as GOUKEI
on MAIN.PF_CODE = GOUKEI.PF_CODE
group by MAIN.PF_CODE, MAIN.PF_NAME
order by "合計人数" desc, 都道府県コード