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 3 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 F.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 P.PF_CODE = F.PF_CODE
  WHERE F.NATION_CODE != '113'
 )AS SUB
 
 GROUP BY SUB.PF_CODE,SUB.PF_NAME
 
 ORDER BY 合計人数 DESC,
 都道府県コード ASC;