SELECT
PF_CODE AS 都道府県コード
,PF_NAME AS 都道府県名
,MAX(CASE SUB.RANK WHEN 1 THEN NATION_NAME ELSE NULL END) AS "1位 国名" 
,MAX(CASE SUB.RANK WHEN 1 THEN AMT ELSE 0 END) AS "1位 人数" 
,MAX(CASE SUB.RANK WHEN 2 THEN NATION_NAME ELSE NULL END) AS "2位 国名" 
,MAX(CASE SUB.RANK WHEN 2 THEN AMT ELSE 0 END) AS "2位 人数" 
,MAX(CASE SUB.RANK WHEN 3 THEN NATION_NAME ELSE NULL END) AS "3位 国名" 
,MAX(CASE SUB.RANK WHEN 2 THEN AMT ELSE 0 END) AS "3位 人数"
,SUM(AMT) AS 合計人数
FROM(
SELECT
F.PF_CODE AS PF_CODE
,P.PF_NAME AS PF_NAME
,N.NATION_NAME AS NATION_NAME
,F.AMT AS AMT
,
RANK() OVER(
PARTITION BY PF_CODE
ORDER BY AMT DESC,F.NATION_CODE) AS RANK
FROM
FOREIGNER AS F
INNER JOIN
NATIONALITY AS N
ON N.NATION_CODE =F.NATION_CODE
INNER JOIN
PREFECTURE AS P
ON F.PF_CODE = P.PF_CODE
WHERE NATION_CODE <> '113'
) AS SUB
GROUP BY
SUB.PF_CODE,SUB.PF_NAME
ORDER BY 合計人数DESC
,都道府県コード ASC;