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