WITH X AS (
select FR.PF_CODE,PR.PF_NAME,FR.NATION_CODE,NA.NATION_NAME,SUM(AMT) SUMAMT,RANK() OVER (PARTITION BY FR.PF_CODE ORDER BY SUM(AMT) DESC)  RNK from FOREIGNER FR INNER JOIN NATIONALITY NA ON (NA.NATION_CODE = FR.NATION_CODE AND NA.NATION_CODE != '113') INNER JOIN PREFECTURE PR ON (PR.PF_CODE = FR.PF_CODE) GROUP BY FR.PF_CODE,FR.NATION_CODE ORDER BY FR.PF_CODE,SUM(AMT) DESC
) 
select FR1.PF_CODE 都道府県コード,
PR.PF_NAME 都道府県名,
X1.NATION_NAME '1位 国名',X1.SUMAMT '1位 人数',
X2.NATION_NAME '2位 国名',X2.SUMAMT '2位 人数',
X3.NATION_NAME '3位 国名',X3.SUMAMT '3位 人数',
SUM(FR1.AMT) AS 合計人数 
FROM FOREIGNER FR1
 INNER JOIN PREFECTURE PR ON (PR.PF_CODE = FR1.PF_CODE)
 LEFT OUTER JOIN X X1 ON (X1.PF_CODE = FR1.PF_CODE AND X1.RNK = 1)
 LEFT OUTER JOIN X X2 ON (X2.PF_CODE = FR1.PF_CODE AND X2.RNK = 2)
 LEFT OUTER JOIN X X3 ON (X3.PF_CODE = FR1.PF_CODE AND X3.RNK = 3)
WHERE FR1.NATION_CODE != '113' 
GROUP BY FR1.PF_CODE
ORDER BY SUM(FR1.AMT) DESC
;