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