select 
A.PF_CODE AS 都道府県コード,
PR.PF_NAME AS 都道府県名,
A.FST_CODE AS "1位 国名",
A.FST_AMT AS "1位 人数",
A.S_CODE AS "2位 国名",
A.S_AMT AS "2位 人数",
A.T_CODE AS "3位 国名",
A.T_AMT AS "3位 人数",
B.SUM_AMT AS "合計人数"
from (
select 
PF_CODE,
MAX(CASE WHEN RN = 1 then NATION_NAME ELSE "" end) as FST_CODE,
MAX(CASE WHEN RN = 1 then SUM_AMT end)  as FST_AMT,
MAX(CASE WHEN RN = 2 then NATION_NAME ELSE "" end)  as S_CODE,
MAX(CASE WHEN RN = 2 then SUM_AMT end)  as S_AMT,
MAX(CASE WHEN RN = 3 then NATION_NAME ELSE "" end)  as T_CODE,
MAX(CASE WHEN RN = 3 then SUM_AMT end)  as T_AMT
from (
select
PF_CODE,
NATION_NAME,
SUM_AMT, 
RANK() OVER (PARTITION BY PF_CODE ORDER BY SUM_AMT desc, NATION_CODE) AS RN
from (
select 
PF_CODE,
NATION_NAME,
FO.NATION_CODE,
sum(AMT) AS SUM_AMT 
from 
FOREIGNER AS FO
inner join NATIONALITY NA on NA.NATION_CODE = FO.NATION_CODE
where NA.NATION_CODE != "113"
group by 
PF_CODE, NATION_NAME, FO.NATION_CODE
) ) where RN <= 3
group by PF_CODE) AS A
INNER JOIN (
select 
PF_CODE,
sum(AMT) AS SUM_AMT 
from 
FOREIGNER
where NATION_CODE != "113"
group by 
PF_CODE ) AS B ON A.PF_CODE = B.PF_CODE
inner join PREFECTURE PR ON PR.PF_CODE = A.PF_CODE
order by B.SUM_AMT desc
,A.PF_CODE