WITH
 TMP AS (
  SELECT
    PF_CODE,
    NATION_CODE,
    AMT,
    RANK() OVER (
      PARTITION BY PF_CODE
      ORDER BY AMT DESC
    ) AS MY_RANK
  FROM
  (
    SELECT * FROM FOREIGNER WHERE NATION_CODE <> 113
  )
)
SELECT
  PREFECTURE.PF_CODE AS `都道府県コード`,
  PREFECTURE.PF_NAME AS `都道府県名`,
  (SELECT NATION_NAME FROM NATIONALITY WHERE NATION_CODE IN (SELECT NATION_CODE FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 1)) AS `1位 国名`,
  (SELECT AMT FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 1) AS `1位 人数`,
  (SELECT NATION_NAME FROM NATIONALITY WHERE NATION_CODE IN (SELECT NATION_CODE FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 2)) AS `2位 国名`,
  (SELECT AMT FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 2) AS `2位 人数`,
  (SELECT NATION_NAME FROM NATIONALITY WHERE NATION_CODE IN (SELECT NATION_CODE FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 3)) AS `3位 国名`,
  (SELECT AMT FROM TMP WHERE TMP.PF_CODE = PREFECTURE.PF_CODE AND MY_RANK = 3) AS `3位 人数`,
  SUM(FOREIGNER.AMT) AS `合計人数`
FROM
  TMP
INNER JOIN
  PREFECTURE ON TMP.PF_CODE = PREFECTURE.PF_CODE
INNER JOIN
  FOREIGNER ON PREFECTURE.PF_CODE = FOREIGNER.PF_CODE
GROUP BY
  PREFECTURE.PF_CODE
ORDER BY
  `合計人数` DESC, PREFECTURE.PF_CODE