select
p.PF_CODE as "都道府県コード"
,p.PF_NAME as "都道府県名"
,x1.NATION_NAME as "1位 国名"
,x1.AMT as "1位 人数"
,x2.NATION_NAME as "2位 国名"
,x2.AMT as "2位 人数"
,x3.NATION_NAME as "3位 国名"
,x3.AMT as "3位 人数"
,xsum.AMT as "合計人数"
from 
PREFECTURE p,
(
select
f1.PF_CODE
,n.NATION_NAME
,f1.AMT
from
(
select 
f.PF_CODE
,RANK() OVER(PARTITION BY f.PF_CODE ORDER BY f.AMT desc,f.NATION_CODE asc) as order_num
,f.NATION_CODE
,f.AMT
from 
FOREIGNER f
where f.NATION_CODE<>113
) f1 , NATIONALITY n
where f1.order_num=1
and f1.NATION_CODE=n.NATION_CODE
)x1

left join 
(
select
f1.PF_CODE
,n.NATION_NAME
,f1.AMT
from
(
select 
f.PF_CODE
,RANK() OVER(PARTITION BY f.PF_CODE ORDER BY f.AMT desc,f.NATION_CODE asc) as order_num
,f.NATION_CODE
,f.AMT
from 
FOREIGNER f
where f.NATION_CODE<>113
) f1 , NATIONALITY n
where f1.order_num=2
and f1.NATION_CODE=n.NATION_CODE
)x2
on 
x1.PF_CODE=x2.PF_CODE

left join 
(
select
f1.PF_CODE
,n.NATION_NAME
,f1.AMT
from
(
select 
f.PF_CODE
,RANK() OVER(PARTITION BY f.PF_CODE ORDER BY f.AMT desc,f.NATION_CODE asc) as order_num
,f.NATION_CODE
,f.AMT
from 
FOREIGNER f
where f.NATION_CODE<>113
) f1 , NATIONALITY n
where f1.order_num=3
and f1.NATION_CODE=n.NATION_CODE
)x3
on 
x1.PF_CODE=x3.PF_CODE

left join 
(
select 
f.PF_CODE
,sum(f.AMT) as AMT
from 
FOREIGNER f
where f.NATION_CODE<>113
group by f.PF_CODE
)xsum
on 
x1.PF_CODE=xsum.PF_CODE


where
p.PF_CODE=x1.PF_CODE

order by "合計人数" desc,"都道府県コード" asc